VBA to delete rows based on one specific cell

mel_d

New Member
Joined
Nov 29, 2018
Messages
10
I'm new to VBA and can't figure this out. I need excel to look at cell A2 and delete rows 3-50 if cell A2 is blank. Can someone help me out with a code for this? Also, is there a way that excel can do this automatically, without having to run a macro. For example if I delete cell A2, it automatically deletes rows 3-50?

Any info is appreciated! TIA
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You can do it automatically but it requires VBA. Its dangerous though as there is no undo once the code runs. If you clear, not delete, the cell the rows and whatever is in them is gone. Anyway right click the sheet tab. Press view code. Paste in this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "A2" Then Exit Sub
If Len(Target.Value) = 0 Then Rows("3:50").Delete Shift:=xlUp

End Sub
 
Upvote 0
You can do it automatically but it requires VBA. Its dangerous though as there is no undo once the code runs. If you clear, not delete, the cell the rows and whatever is in them is gone. Anyway right click the sheet tab. Press view code. Paste in this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "A2" Then Exit Sub
If Len(Target.Value) = 0 Then Rows("3:50").Delete Shift:=xlUp

End Sub

So this worked in a very simple spreadsheet. I was trying to simplify my questions. I have lots of merged cells, does this code not work then? Because when I instered it into my more elaborate spreadsheet with formatting, it did nothing...
 
Upvote 0
Merged cells are never a good idea. Is A2 encapsulated within a merged cell?
 
Upvote 0
Ok, so I was able to do it once cells unmerged. Just one more question.

If I want to add another function to the same sheet, how do I do that? Here's what I'm trying to do in addition:

As before, if A2 is empty, delete rows 3 to 50 (code above is fine). But in addition, if A2 is not empty (hence nothing is deleted), I would like to check for cell A22 and if that one is empty, then delete rows 50 to 70.

So if A2 is empty, delete rows 3-50
But if A2 is not empty, check for A22
If A22 is empty, delete rows 50-70

All automatic, just like before.

THANKS so much for your help!
 
Upvote 0
Is that A22 being empty before you delete the rows or after you delete the rows?
 
Upvote 0
Is that A22 being empty before you delete the rows or after you delete the rows?

It's empty before I delete the rows. Basically I have a report that is generated by an application and I'm uploading an excel template. The cells are populated in the app and then printed in Excel. The excel template I'm making needs to account for all fields that are present in the app but sometimes, the user will not be entering anything in a field, thus it will be empty when the report is generated. I want Excel to only print the pages of a sheet that have information on them but because the template I'm uploading has several pages, Excel prints all of them, even if they are empty. This is the only way I was able to figure out Excel NOT printing empty pages - run a macro that looks to the top of the page where the heading is, if this is empty, delete all subsequent pages. I need to do this on each page though since there are 5. So I need the macro to repeat:

If A2 is empty, then delete rows below
If A2 is not empty, look at A22 and delete rows below
If A22 is not empty, look at A44 and delete rows below, etc.

Hope this makes sense. I'm happy to hear if there is an easier version of what I want to do. Basically make sure that when the report generates (e.g. user submits a survey from the app), Excel only prints those pages that contain visible cells. The template has 5 pages on each sheet that are all formatted (most is conditional formatting) so when there is only visible information on page 1, the report generated should not be displaying 4 empty pages after the first page.
 
Upvote 0
So you just want to look at A2, then A22,A44,A66,A88 etc and delete down from whichever is empty first? How can we identify which is the maximum row number that we need to test?
 
Upvote 0
So you just want to look at A2, then A22,A44,A66,A88 etc and delete down from whichever is empty first? How can we identify which is the maximum row number that we need to test?

Yup, look at whichever is empty first and just delete everything below it. The max number of rows I have is 300 so we can say
if A2 is empty, delete A2-A300
if A22 is empty, delete A22-A300
if A44 is empty, delete A44-A300 etc.
 
Upvote 0
Yup, look at whichever is empty first and just delete everything below it. The max number of rows I have is 300 so we can say
if A2 is empty, delete A2-A300
if A22 is empty, delete A22-A300
if A44 is empty, delete A44-A300 etc.

I should also mention that it might not always be this exact scenario. It might also be that

if A22 is empty, delete A15-A300, etc. (not necessarily starting the delete from the empty lookup cell).
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top