How to detect a blank range and paste data?
I have a document that I am trying to format so it works as a source for a pivot table.
Currently it has too much data in column A, so my Macro goes through column A to insert cells to adjust data over so it can be properly nested in categories. This creates blank cells where I then want to copy the data above it and paste so everything is labeled correctly on the pivot table. I'd like to paste to the entire range of blank cells in columns A and B, so I don't need to check each cell each time. This spreadsheet is over 5k lines, so it takes ~10 minutes to run currently.
Original Data:
| Award/Grant | Budget | Current Actuals | LTD Actuals |
|---|---|---|---|
| AWD-154 | 50,000 | 12,500 | 40,000 |
| GR234 | 25,000 | 600 | 1,500 |
| Obj-123 | 400 | 20 | 250 |
Column corrected data:
| Award | Grant | Object | Budget | Current Actuals | LTD Actuals |
|---|---|---|---|---|---|
| AWD-154 | 50,000 | 12,500 | 40,000 | ||
| GR234 | 25,000 | 600 | 1,500 | ||
| Obj-123 | 400 | 20 | 250 |
Now I copy the AWD cell to the one below it, next to Grant, and delete the original line. since I don't care about the AWD total row.
| Award | Grant | Object | Budget | Current Actuals | LTD Actuals |
|---|---|---|---|---|---|
| AWD-154 | GR234 | 25,000 | 600 | 1,500 | |
| Obj-123 | 400 | 20 | 250 |
I can have anywhere from 2 - 10+ Obj rows here, so I'd rather paste to the entire range instead of line by line as I'm doing now.
.... ThirdDone: Cells(13, 2).Select limit = 0 Do Until limit = 200 If ActiveCell.Value = "" Then ActiveCell.Offset(-1, 0).Select ActiveCell.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAllMergingConditionalFormats ActiveCell.Offset(1, 0).Select limit = 0 Else If ActiveCell.Value = "Total" Then GoTo ThirdDone Else limit = limit + 1 ActiveCell.Offset(1, 0).Select End If End If Loop End Sub [link] [comments]
Want to read more?
Check out the full article on the original site