excel empty rows delete 3

[leijen]

Hi
If i have data in cells a1 to f5 and rows 2 and 4
have no data how can I delete the empty rows using VBA?

Thanks

Sam

 

[PepeLeMokko]

You can do it without VBA
Select the entire table
Data > Filter > Autofilter and choose " Non blanks" in the dropdown lists.
You can then copy paste the result where you like.
You can also " Record Macro" while doing this, it would give you the code

 

[KenWright]

Select the range in question and run the following:-

Public Sub DeleteReallyBlankRows()
'Chip Pearson
'Will delete all rows that are entirely blank
Dim r As Long
Dim c As Range
Dim n As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For r = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(r).EntireRow) = 0 Then
rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards
Ken.................

 

[Bowers74]

Just to add my two cents worth here are two solutions you could use:

Another non-VBA solution:

1. Select Column A:A
2. Press Ctrl+G
3. select Special...
4. Select Blanks
5. Right Click one of the selected blanks
6. Select Delete...
7. Select Entire Row
8. Press OK

You can record the above into a macro too. ;-)

Another VBA solution:


Sub DeleteBlanks()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Range("A65536&quot.End(xlUp).Row
Range("A1&quot.Select
Do Until ActiveCell.Row = LastRow
If Application.WorksheetFunction.CountA(Rows(ActiveCell.Row)) = 0 Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A1&quot.Select
Application.ScreenUpdating = True
End Sub

I hope these help!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[KenWright]

Just to caveat though Mike, the first only works if you can really depend on the entire row being blank if just that cell in that column is blank.

As long as it is though, it's how I usually tackle mine.

Regards
Ken................