Delete blank rows if more than one

[Kjellk]

I have groups of data divided by blank rows. It is not consistent- sometimes
its one row, sometimes it is more. I want to to keep one blank row between
every group and remove the others.

 

[Jacob Skaria]

Use the below macro. If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). From workbook launch VBE using
short-key Alt+F11. From menu 'Insert' a module and paste the below code.
Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro()>

Sub Delete2EmptyRows()
Dim lngTemp As Long, lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountBlank(Rows(lngRow)) = Columns.Count Then
If lngTemp = lngRow + 1 Then Rows(lngTemp).Delete
lngTemp = lngRow
End If
Next
End Sub


If this post helps click Yes

 

[Kjellk]

Hi
Just perfect!
Regards
Kjell

Use the below macro. If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). From workbook launch VBE using
short-key Alt+F11. From menu 'Insert' a module and paste the below code.
Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro()>

Sub Delete2EmptyRows()
Dim lngTemp As Long, lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountBlank(Rows(lngRow)) = Columns.Count Then
If lngTemp = lngRow + 1 Then Rows(lngTemp).Delete
lngTemp = lngRow
End If
Next
End Sub


If this post helps click Yes

 

[Jacob Skaria]

Cheers.

If this post helps click Yes

 

[Shane Devenshire]

Hi,

Don't really need a macro, but you could record these steps if you wanted:

1. Suppose column A contains data except when you have blank rows, then in
an empty column, lets say D, in D2 enter the formula
=IF(AND(A1="",A2=""),"Y",1)
and copy it down as far as you data goes.
2. Select this range of formulas and press F5, Special, Formulas, and
unchech all except Text, click OK
3. Press Ctrl+- (control minus) and choose Entire row, OK.
4. Clear the formulas from column D

The code to do this runs extremely fast

Sub DeleteExcess()
Dim Bottom As Long
Bottom = [A65536].End(xlUp).Row
Range("D2" & Bottom).Select
Selection = "=IF(AND(A2="""",A1=""""),""Y"",1)"
Selection.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
Columns("D").ClearContents
End Sub

 

[FARAZ QURESHI]

Instead of =IF(AND(A1="",A2=""),"Y",1) in the first point, use:
=IF(AND(isblank(A1),isblank(A2)),"Y",1)

Hi,

Don't really need a macro, but you could record these steps if you wanted:

1. Suppose column A contains data except when you have blank rows, then in
an empty column, lets say D, in D2 enter the formula
=IF(AND(A1="",A2=""),"Y",1)
and copy it down as far as you data goes.
2. Select this range of formulas and press F5, Special, Formulas, and
unchech all except Text, click OK
3. Press Ctrl+- (control minus) and choose Entire row, OK.
4. Clear the formulas from column D

The code to do this runs extremely fast

Sub DeleteExcess()
Dim Bottom As Long
Bottom = [A65536].End(xlUp).Row
Range("D2" & Bottom).Select
Selection = "=IF(AND(A2="""",A1=""""),""Y"",1)"
Selection.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
Columns("D").ClearContents
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I have groups of data divided by blank rows. It is not consistent- sometimes
its one row, sometimes it is more. I want to to keep one blank row between
every group and remove the others.