Hopefully easy question

[pofe333]

I copied an Excel macro from a previous post to check cells in column A for data, and if no data is found there to delete the row. The code is below, but how can I modify this to check cells in column B or C instead:

-----------------------------------
Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub
-----------------------------------

Thanks in advance!

 

[SOS]

For Column B:

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, [COLOR=red][B]2[/B][/COLOR]).End(xlUp).Row To 1 Step -1
If Cells(r, [B][COLOR=red]2[/COLOR][/B]) = "" Then Rows(r).Delete
Next r
End Sub

For C:

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, [B][COLOR=red]3[/COLOR][/B]).End(xlUp).Row To 1 Step -1
If Cells(r, [B][COLOR=red]3[/COLOR][/B]) = "" Then Rows(r).Delete
Next r
End Sub

 

[pofe333]

Thanks!! I thought it would be pretty easy, but I'm an idiot

 

[pbaldy]

The second argument of Cells(R, C) is the column, so change the 1 to 2 or 3.

 

[pbaldy]

Geez, am I slow or what?!?

 

[pofe333]

Thanks to you both. Followup if you don't mind... I have a different macro that deletes Rows 1-13, then adds a Date column. Is there a way to have these run consecutively, or should I just run them individually? I'm not too familiar with macros.

 

[Brianwarnock]

Strange to have a macro that deletes rows 1to 13 come what may, and mightn't those rows change if you run the other macro first or simultaneously?

Brian

 

[pofe333]

Strange to have a macro that deletes rows 1to 13 come what may, and mightn't those rows change if you run the other macro first or simultaneously?

Brian

Funny you mention that. I just happened to notice that the macro checking for cell data in the columns will negate the need for my first macro. I would still need to add a column at the beginning for date though, but I guess I could do that manually.

 

[Brianwarnock]

The 3 macros you have for deleting rows if a cell is blank, are you going to run them consecutively, or are they required on different occasions?

Brian

 

[pofe333]

Different occasions. For this particular spreadsheet, I only need it to delete all rows that don't have data in Column C, then add a column with "Date" as the header.

What's weird is that when I run the macro to delete rows, everything works perfectly up to the last row. For some reason, it doesn't touch the last row. If I put data in the row below it (making it the next to last row), the macro will pick it up.

 

[Brianwarnock]

Funny you should mention that, as I was looking at the macro I realised that the last row to be checked would be the last row with data in colA, the macro needs changing. hmm I'm a bit rusty I wont trust air code so give me a few minutes.

Brian

 

[Brianwarnock]

Corrected code

Sub DeleteBlankARows()
Dim r As Long
Dim lastrow As Long
With ActiveSheet.UsedRange
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
End With

For r = lastrow To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub

rian

 

[pofe333]

Corrected code

Sub DeleteBlankARows()
Dim r As Long
Dim lastrow As Long
With ActiveSheet.UsedRange
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
End With

For r = lastrow To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub

rian

Thanks a ton! I'll try it shortly.

 

[pofe333]

Worked like a charm! Thanks!!

 

[Brianwarnock]

Glad to have helped

Brian