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

 

[Rick Rothstein]

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.

First off, as Fluff suggested... try the code mumps posted or the variation of it that I posted in Message #8 . Now, to answer your question as to what you tried and why it did not work... Null is not a "value" that you can test against using a logical operator. Besides, cells are never Null, rather, they are Empty when they contain nothing. Null is something you can assign to a variable and then test for using the IsNull function. The test you should have performed is this...

If IsEmpty(Cells(i, "M").Value Then

The distinction between Null and Empty is kind of subtle... if you read the Help files for the IsNull and IsEmpty functions, you may gain a clearer understanding of these keywords. But again, I would strongly urge you to use mumps or my variation of his code instead.

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

You do not need a loop with the code mumps posted nor with the variation of it that I posted in Message #8 ... the codes are complete as posted.

 

[Guzzlr]

[CODE
]Option Explicit
Sub Reconcile()

Application.ScreenUpdating = True

Sheets("Summary").Select
With Selection
'copy and paste
Columns("B:C").Select
Selection.Copy
Columns("M:N").Select
Selection.PasteSpecial Paste:=xlPasteValues
End With

Intersect(Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow, Columns("M:N")).Delete xlUp

Application.ScreenUpdating = False


End Sub
[/CODE]

So I have this for my code. However, the blank spaces in Column M are not deleting, along with the cell next to the blank cell min M.

For example, if I have a ACE-1D in M1, and a value of 601 in N1, and no values in the range (M2:M7), but there are values in (N2:N7) then the Range (M2:N7)should delete row by row, and move up.
The next value of M8 has a value of ACI-2 and a value of 572 in N9. However, no values in M10:M15, so cells M10:N15 would delete and move up. and so on until last row in column M.
Thanks

 

[Rick Rothstein]

[CODE
]Option Explicit
Sub Reconcile()

Application.ScreenUpdating = True

Sheets("Summary").Select
With Selection
'copy and paste
Columns("B:C").Select
Selection.Copy
Columns("M:N").Select
Selection.PasteSpecial Paste:=xlPasteValues

End With

With Intersect(Selection, ActiveSheet.UsedRange)
.Value = .Value
End With

Intersect(Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow, Columns("M:N")).Delete xlUp

Application.ScreenUpdating = False


End Sub
[/CODE]

So I have this for my code. However, the blank spaces in Column M are not deleting, along with the cell next to the blank cell min M.

Do you have formulas in Column B some of which are returning the empty text string ""? If so, the I believe those empty text strings are also copied meaning your cells are not truly blank. See if adding the three lines of code I show above in red makes it work correctly for you.

 

[Guzzlr]

Do you have formulas in Column B some of which are returning the empty text string ""? If so, the I believe those empty text strings are also copied meaning your cells are not truly blank. See if adding the three lines of code I show above in red makes it work correctly for you.

That was the ticket, I did have a V-Lookup in Column B
It's working correctly.
Thanks for everyone's help

 

[Rick Rothstein]

That was the ticket, I did have a V-Lookup in Column B
It's working correctly.
Thanks for everyone's help

By the way, you can write your macro more efficiently as well as more compactly like this...

[table="width: 500"]
[tr]
	[td]Sub Reconcile()
  With Range("B1:C" & Columns("B:C").Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row)
    .Offset(, 11).Value = .Value
  End With
  Intersect(Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow, Columns("M:N")).Delete xlUp
End Sub[/td]
[/tr]
[/table]