Macros

Hi Rachael

Thanks for your question.

Yes you can use a macro to delete blank rows. You will have to write the code rather than record it. Here is an example where the data is in a tabular format starting in cell A1.

Sub DelBlanks()

Range("A10000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select

Do Until ActiveCell = Range("A1")
If ActiveCell = "" Then
Selection.Delete Shift:=xlUp
End If
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Loop

End Sub

Notes:
If your data contains more than 10000 rows then adjust the first line to go below the last row.

If your data starts in a cell another cell such as C1 then replace A10000 with C10000 and A1 with C1.

You also can delete blank rows without a macro by using Autofilter provided the data is in a tabular format.

Follow these steps
1. Select all columns of your data
2. Select Data, Filter
3. In any column untick blanks
4. Copy and paste the selection to a new worksheet or to a blank cell to the right.

The pasted data will be without the blanks.

You could create this process as a macro. This code copies the filtered data to a new worksheet.

Sub RemoveBlanks()

Columns("A:D").Select
Selection.AutoFilter
ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

Notes:
Adjust the first line A:D to suit your data.
The macro copies the formatting as well but does not include the blank rows.

Hope either of these methods help.

Regards
Doug
STL