delete range of cells if cells have a null value

[Guzzlr]

LastRow = Range("M:M").Cells.Find("*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastRow = .Range("M" & .Rows.Count).End(xlUp).Row

For i = 9 To LastRow
    If Application.IsNA(Cells(i, "M")) Then

If Cells(i, "M").Value = Null Then
Range("M:N").Delete
'Stop
End If

Hello All,
A little help please.
I'm having trouble with finding empty cells and deleting the empty cells, and moving them up. For example, going row-by-row, in column M, if the cells in column M values is null, then the cells of M:N in that particular row, would be deleted and moved up.
I keep getting a warning for using the word "Row"
can someone tell me where my code is wrong?
Thank you

 

[mumps]

Try:

Sub DeleteCells()
    Application.ScreenUpdating = False
    Columns("M").SpecialCells(xlCellTypeBlanks).Offset(0, 1).Delete shift:=xlUp
    Columns("M").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    Application.ScreenUpdating = True
End Sub

The macro assumes that by "null" you mean blank with no value or formula.

 

[silentwolf]

Hi,

    Dim Zeile As Long
    Dim ZeileMax As Long
    
    With ActiveSheet
        ZeileMax = .UsedRange.Rows.Count
        
        For Zeile = ZeileMax To 2 Step -1
            If Application.WorksheetFunction.CountA(.Rows(Zeile)) = 0 Then
                .Rows(Zeile).Delete
            End If
        Next Zeile
    End With
End Sub

This code would delete all Rows in the used range are empty so maybe this code get you started..

HTH

 

[SpillerBD]

null s probably not covering all your cases...

 

[Fluff]

You say you want to delete "Empty" cells but your code is checking to see if the cell contains #N/A.
Are the cells truly empty?

 

[Guzzlr]

Dim MyRow As Integer
 Dim i As Integer
 MyRow = ActiveSheet.Range("M10000").End(xlUp).Row
 
For i = 9 To MyRow
    If Cells(i, "M").Value = Null Then
        Range("M:N").Delete Shift:=xlUp
    End If
Next i

I've played around with it since I originally posted.
To Fluff's point, my mistake, I'm looking for nothing in the cell, it's blank, which is why I used Null. N/A was wrong...sorry
My code above is sort of like silentwolf's, but my code is still not working...and I don't know why.
Thanks

 

[Fluff]

Have you tried mumps code?

 

[Rick Rothstein]

Try:

Sub DeleteCells()
    Application.ScreenUpdating = False
    Columns("M").SpecialCells(xlCellTypeBlanks).Offset(0, 1).Delete shift:=xlUp
    Columns("M").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    Application.ScreenUpdating = True
End Sub

The macro assumes that by "null" you mean blank with no value or formula.

You do not need to turn off screen updating as the deletions should happen quickly. Also, you can combine the two column deletions into a single code line....

[table="width: 500"]
[tr]
	[td]Sub DeleteCells()
  Intersect(Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow, Columns("M:N")).Delete xlUp
End Sub[/td]
[/tr]
[/table]

 

[mumps]

Thank you Rick. Makes perfect sense and very efficient.

 

[Guzzlr]

Have you tried mumps code?

Dim MyRow As Integer
 Dim i As Integer
 MyRow = ActiveSheet.Range("M10000").End(xlUp).Row

For i = 9 To MyRow
    Columns("M").SpecialCells(xlCellTypeBlanks).Offset(0, 1).Delete shift:=xlUp
    Columns("M").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    Application.ScreenUpdating = True
Next i

I did try mumps (above) but the loop did not work, and Range M with nothing in the cell did not delete Range (M:N)
Thanks