products, sales regions, etc.) with several corresponding Key Performance Indicators (e.g.prices, costs of goods sold, sales, etc.) and you want to show this in a table on your management dashboard.Make sure you have downloaded KPI Dashboard solution workbook to learn this better.
But what if the user of your dashboard wants to scroll down the table and see the rest of the data?
Sure, you might teach him to go to the sheet with the data and scroll up and down there.
This macro will move cells up if there is an empty cell above and column A cell is empty. Column For r = Rcnt To 2 Step -1 If Is Empty(Cells(Rcnt, 1)) Then For c = 1 To Ccnt If Not Is Empty(Cells(r, c)) Then If Not Is Empty(Cells(r - 1, c)) Then Go To notthis End If Next c For c = 1 To Ccnt If Not Is Empty(Cells(r, c)) Then Cells(r - 1, c) = Cells(r, c) End If Next c Cells(r, 1). Screen Updating = True End Sub The Del Cells Up macro will delete all empty cells within the highlighted range and move the cells and formatting up from below.
Data at the right tests more than just what can happen with Excel 95 pasting. Calculation = xl Manual '--Excel 95 Dim Rcnt As Long, Ccnt As Long, r As Long, c As Long Dim Curr Cell As Range On Error Resume Next Selection. Cells deleted will only be deleted from within range; but cells moved up includes cells from below range. Calculation = xl Calculation Automatic Application. Screen Updating = True End Sub Remove content of cells with constants that look empty, but are not, David Mc Ritchie, 2003-07-23, worksheet.functions -- not deleting the cells which would cause cells to move up, but only remove the constants so that they are in fact EMPTY.
At Chandoo.org, I have one goal, "to make you awesome in excel and charting".