Help with Delete Blank Rows code (macro)

[Mister H]

Hi All:

I have been using the following code (see below) that I got from a Board Member (THANKS) to Delete Blank Rows in my Worksheet. It also pops up a message to give the User one last chance to terminate the Delete. It works excellently...

My problem is that I now have added data to the Right of my Ranges that will always be populated.

QUESTION: I was wondering if anyone knows how to tweak this code so that it checks the row but only within the named Range (e.g. one Range is titled "AR") OR if it can check the rows but only up to column J. So this would mean that if columns A to J are Blank then delete that row.

I am not very knowledgeable in VBA but I am pretty sure that this is the line that needs changing:

If Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete


I am not sure how to go about it...


HERE IS THE ORIGINAL CODE:

Sub DeleteARBlankRows()

' (FINAL)


ActiveSheet.Unprotect
Answer = MsgBox("WARNING!  Do you really want to Delete the Blank Rows?" & vbCr & vbCr & "PLEASE DO NOT DELETE the Blank Rows if you have not entered any data into this section as it may render the sheet unusable." & vbCr & vbCr & "If you are not using this section PLEASE click the HIDE buttion instead." & vbCr & vbCr & "If you still want to DELETE these lines than CLICK OK otherwise Click Cancel.", vbOKCancel, "WARNING!")
If Answer = vbCancel Then Exit Sub

Dim R As Long
Dim rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set rng = Range("AR")

For R = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete
End If
Next R

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

THANKS to anyone that can help.

Bye 4 Now,
Mark

 

[pgc01]

Hi Mark

Try changing the IF:

If Application.WorksheetFunction.CountA(rng.Rows(R)) = 0 Then 
rng.Rows(R).EntireRow.Delete

HTH
PGC

 

[Mister H]

THANKS pgc01.

I will try that out and see if it works for me. Your assistance is VERY MUCH appreciated...

Take Care,
Mark

 

[Mister H]

SORRY I should have tested it before I replied. This does as I asked but it creates other problems for me. I need the last 2 rows of the Range to remain. There is one row that has no data but it does have to remain there. If you can alter your code slightly to keep the last 2 rows in the Range that would be AWESOME. In the meantime I will try to come up with my own workaround by changing the way I have set up my sheet. I prefer not to alter my shet if possible.

THANKS Again,
Mark

 

[PolarBear]

That should be easy - your delete loop starts at the bottom of the range and works its way up. So just adjust the loop counter:

For R = rng.Rows.Count - 2 To 1 Step -1

 

[Mister H]

THANKS PolarBear... I will try that out tomorrow when I get back to the office.

Bye 4 Now,
Mark

 

[just_jon]

Is there any one field in your rows that, if the row contains any data is guaranteed to contain a value?