Hi,
I have statement for vendor the range is A17:I80, with column I containing formulas what I want VBA that deletes rows after last used row in that range even though have formulas but no value.
I don't know much about VBA been searching internet without success cause all ones found just look for blanks because have formulas won't delete them.
Any suggestions,
Thank you
Any help?
Wow! no one can help. what a forum.
Hi Jose,
Posting a sample file will always help you get a faster result, otherwise we will have to recreate your structure and that will always be different than what you have.
Try this code:
Sub DeleteRows()
Dim Wks as worksheet, DeleteRng as range, i as long
Set Wks=ThisWorkbook.Worksheets("Sheet1")
For i=17 To 80
If LEN(Wks.Cells(i,"I").Value)=0 then
If DeleteRng is nothing then
Set DeleteRng=Wks.Cells(i,"I")
Else
Set DeleteRng=Union(DeleteRng,Wks.Cells(i,"I"))
End if
End If
Next i
If Not DeleteRng is Nothing then DeleteRng.EntireRow.Delete
End Sub
By the way, you could try searching our forum, you have solutions very close to your case:
Welcome to our forum.
thank you.
I tried the code but getting compile error "invalid use of property".
attaching file.
Nothing attached, you have to press the Start Upload button, after Select Files.
Hi Catalin,
Attached now.
Thank you,
Hi Jose,
You did not copied the complete code as posted above, the delete method and the procedure closing statement is missing:
.Delete
End Sub
The code works if copied completely (and of course change Sheet1 to your sheet name - Statement).
Thank you, Catalina.
Question,
If I want to change this:
For i=17 To 80 to a name range instead like "STMTRNG" how do I do it?
Sorry to ask stupid question but that good in VBA.
Thanks again.
Not tested, but should work:
For i=wks.range("STMTRNG").cells(1).row to (wks.range("STMTRNG").cells(1).row + wks.range("STMTRNG").Rows.Count)
Thank you so much, Catalin.
worked perfectly!.
