Delete row if VBA

[jessica_p]

Hi all,

So I'm new to VBA and macro's.
I've done some research on internet, tried multiple macro's as mentioned on the internet, but can't seem to make it work.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Local ID[/TD]
[TD]Global ID[/TD]
[TD]Entity[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1008269[/TD]
[TD]1008269[/TD]
[TD]HHG[/TD]
[/TR]
[TR]
[TD]2851990[/TD]
[TD]979889[/TD]
[TD]PPH[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]289144[/TD]
[TD]999807[/TD]
[TD]PPH[/TD]
[/TR]
[TR]
[TD]849008[/TD]
[TD]819733[/TD]
[TD]HHG[/TD]
[/TR]
[TR]
[TD]2008956[/TD]
[TD]2008956[/TD]
[TD]PPH[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 263"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][TABLE="class: grid, width: 500"]
<tbody></tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
What I want:
If cell A = empty, then delete entire row.

What Macro I have now:


Do

If InStr(Persnr, 0) Then
ActiveSheet.Rows(row_number & ":" & row_number).Delete
row_number = row_number - 1

End If

Loop Until Persnr = ""


However, prior to wanting to run this macro, I've put in a lot of formulas. So if there's an empty cell in A, there's still a formula in.
Even after removing the formulas from the sheet, somehow the cell still isn't visible as being empty.
So maybe there's the issue?

Could somebody please help me on what the macro should be in order for the rows to be deleted?

Thank you in advance,

Jessica

 

[Fluff]

Are you happy for all the formulas on the sheet to be replaced with values?

 

[jessica_p]

Are you happy for all the formulas on the sheet to be replaced with values?

Hi Fluff,

Sure I am.
The end product requires only values, so I was going to replace the formulas with values anyway.

Only "issue" then is that somehow Excel doesn't see the empty cells as being empty (found out via Find - Special).

 

[Fluff]

Ok, how about

Sub Delrws()
   With ActiveSheet.UsedRange
      .Value = .Value
   End With
   With Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[jessica_p]

Perfect! Thank you so much!

So, what was I doing wrong?
I will analyze the formula you inserted, but also would like to know what I did wrong.

 

[Fluff]

At a guess, I suspect that you copied the cells & then did Paste as values (either manually, or with code).
When you do that you will endup with a NullString in any cell where the formula returned ""
Whereas if you do Value=Value that doesn't happen (although I've no idea why)

 

[jessica_p]

Thank you so much Fluff!

 

[Fluff]

You're welcome