Help with Delete Blank Rows code (macro)

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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:

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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Mark

Try changing the IF:

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

HTH
PGC
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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

Board Regular
Joined
Sep 26, 2005
Messages
215

ADVERTISEMENT

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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
THANKS PolarBear... I will try that out tomorrow when I get back to the office.

Bye 4 Now,
Mark
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Is there any one field in your rows that, if the row contains any data is guaranteed to contain a value?
 

Forum statistics

Threads
1,136,353
Messages
5,675,288
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top