Log In
Register
Home
October 25, 2017
Offline
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
See AnswerOctober 25, 2017
Offline
Any help?
October 25, 2017
Offline
Wow! no one can help. what a forum.
November 8, 2013
Offline
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:
https://www.google.com/search?.....038;uact=5
Welcome to our forum.
Answers Post
October 25, 2017
Offline
thank you.
I tried the code but getting compile error "invalid use of property".
attaching file.
November 8, 2013
Offline
Nothing attached, you have to press the Start Upload button, after Select Files.
October 25, 2017
Offline
Hi Catalin,
Attached now.
Thank you,
November 8, 2013
Offline
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).
October 25, 2017
Offline
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.
1 Guest(s)
