If a cell is blank, delete the whole row

[Monsignor]

I don't know why this is so hard.

The goal:

Go through Column R, rows 2 thru 46.
If the cell is empty, delete the whole row, then check the next row in Column R

The number of rows is fixed. It'll always be 2 thru 46.
(My research shows lots of code for variable length ranges)

Among other things, I've tried this:

ActiveSheet.Columns("R").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

What's missing?

 

[Jonmo1]

Try

ActiveSheet.Range("R2:R46").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

If that doesn't work, it means your blanks are not really blank..
Try

With ActiveSheet.Range("R2:R46")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

 

[VoG]

Does this work? If not the cells are not truly blank.

Range("R2:R46").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[Rick Rothstein]

If that doesn't work, it means your blanks are not really blank..
Try

With ActiveSheet.Range("R2:R46")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

I am not sure I like this alternative... it will replace an formulas in the range with constants. I know it is unlikey someone would want to delete rows based on formula blanks, but if that is the case, your suggestion would remove the formulas that would be expected to remain.

 

[Jonmo1]

To me, this always comes up after someone has had formulas returning "", and they have alreeady done the copy - Paste Special - Values.
Then they are trying to delete the blanks..

Sure you're correct that there may be someone that still has the formulas there
But I tend to make my suggestions based on the most likely scenario.

If this IS the case, then the alternative is looping the range and testing for ""
Or Autofilter

 

[Rick Rothstein]

To me, this always comes up after someone has had formulas returning "", and they have alreeady done the copy - Paste Special - Values.
Then they are trying to delete the blanks..

Sure you're correct that there may be someone that still has the formulas there
But I tend to make my suggestions based on the most likely scenario.

If this IS the case, then the alternative is looping the range and testing for ""
Or Autofilter

OR, staying with your idea, but modifying it so as to not affect Column R...

Dim UnusedColumn As Range
Set UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Offset(, 1).EntireColumn
With Intersect(Rows("2:46"), UnusedColumn)
  .FormulaR1C1 = "=IF(RC18="""",""X"","""")"
  .Value = .Value
  .SpecialCells(xlConstants).EntireRow.Delete
End With

 

[Monsignor]

That was exactly the problem: my cells weren't really blank/empty. I used
=ISBLANK(R2) and determined that none of the cells in the column were TRUE until R47.

As jonmo1 suggests, this is happening after a Past Values. The cells look empty and there are no spaces but the code isn't ready an empty cell.

The source, Sheet1, does have a formula:

=IF(AND(C8<>"",D8<>"",ISNUMBER(N8)),"c","")

I copy that and pastevalues it onto Sheet2 where the "c" or "" ends up in Column R.


I just need the final list. Don't need the formulas any more. So, losing the formulas isn't a big deal.

jonmo1's code worked fine.

Any lessons to take away from this? Or, just be aware that PasteValues always pastes something?

 

[Jonmo1]

Any lessons to take away from this? Or, just be aware that PasteValues always pastes something?

PasteValues pastes the value of the cell.

The issue is that a formula returning "" is NOT Blank.
It's a Null Text String. Which actually IS a Value.
Even after the paste special - values.

And to be honest, I'm not quite sure why .Value = .Value works..
I would think that the same problem would exist with that..

 

[Monsignor]

Brilliant. Thanks for the correction and for the lesson.
This is one more thing to add to my binder as I learn this VBA thing.

 

[RKlem]

I found this old thread in searching for a solution to my problem.

I am wanting to delete any row that has an empty cell in column A. The problem is that the first several hundred rows are empty but the code that Im using below fails to find them as empty because they are marked as constants.

ActiveSheet.Columns("R").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

How can I delete these rows using VBA if they are constants? If I select these cells then clear them or select delete , then it clears the constant and then I can run the above code and it works. I need to be able to do it with VBA code.


I tried the .Value = .Value and that did not work for me.

I have also used the below code but it didnt work either.

Dim UnusedColumn As Range
Set UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Offset(, 1).EntireColumn
With Intersect(Rows("1:1500"), UnusedColumn)
  .FormulaR1C1 = "=IF(RC18="""",""X"","""")"
  .Value = .Value
  .SpecialCells(xlConstants).EntireRow.Delete
End With

Thanks
Rick