Inserting and Deleting Rows

[cyng]

I get a daily spreadsheet from someone else and they always insert double rows (ie.. Row 1 will have info on it and then they skip Row 2 and type info on Row 3).
How can I delete these empty rows without having to go through the spreadsheet and individually delete each one?

 

[MrKowz]

Give this a go:

Public Sub DeleteBlankRows()
Dim i   As Long, _
    LR  As Long
    
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
    If Range("A" & i).Value = "" Then
        Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[hiker95]

cyng,

You do not have to loop thru each cell and test if it is blank.


Sample data before the macro:

Excel Workbook
	A	B	C	D	E
1	1	1	1	1
2
3	3	3	3	3
4
5	5	5	5	5
6
7	7	7	7	7
8
9	9	9	9	9
10
11	11	11	11	11
12
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	1	1	1	1
2	3	3	3	3
3	5	5	5	5
4	7	7	7	7
5	9	9	9	9
6	11	11	11	11
7
8
9
10
11
12
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub DeleteOnlyBlankRows()
Dim LR As Long
Application.ScreenUpdating = False
With ActiveSheet
  LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  .Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

Then run the DeleteOnlyBlankRows macro.

 

[cyng]

Thank you both for your suggestions. I will try this when I get to work in the morning. I cannot believe it is this difficult just to delete unwanted rows in a spreadsheet. GEEZZZ!

 

[cmhoz]

If you don't have sub-total lines in the middle of your data, you can always just sort the data and the blank rows will go to the bottom

 

[cyng]

Well I have removed all other data that did not pertain to the existing rows of information. You mean to tell me that just doing a simple sort will remove the blank rows to the bottom??

 

[Xx7]

You could data filter, select "blanks", delete them all, unfilter.

 

[cyng]

Thank you all again. I will post tomorrow which was the easiest to take on for what should have been an easy spreadsheet.

 

[cyng]

Well DUH!!! It was that simple; select all - Sort A-Z to take out all the blank rows.
When I get ready for another boneheaded question, I know where to look.