Rebecca has attended:
Access Introduction course
Access Intermediate course
Excel VBA Intro Intermediate course
Deleting rows of Data
Hi,
I'm fairly new to excel VBA and I've got a formulated sheet where I want entire rows of cells that formulate as N/A# to be deleted (these rows could be inbetween other rows of data), can someone help?
Thanks,
Rebecca
RE: Deleting rows of Data
Hi Rebecca
Thanks for your question
I suggest cycling through each cell in your worksheet and for each cell check to see if the cell contains an error and then clear the contents if this is true. I suggest something like the following
[code]If activecell.worksheetfunction.iserror = True then
activecell.clearcontents
End if/code]
Regards
Stephen
RE: Deleting rows of Data
Hi,
Thanks for your suggestion, unfortunately I need it to actually delete the rows as the formula goes on for hundreds of lines but there may only be some data so if it just clears the contents I will have hundreds of blank rows and I was trying to avoid having to manually go and delete the rows.
Thanks,
Rebecca
RE: Deleting rows of Data
Hi Rebecca
Thanks for the update
OK I suggest replacing the clear contents method with the following
ActiveCell.Rows.DeleteThis will delete the entire row in question
Regards
Stephen
RE: Deleting rows of Data
Hi,
I've tried using the code above but get a runtime error of 438 (Object does not support this property or method)?
Maybe I am doing something wrong?
RE: Deleting rows of Data
Hi Rebecca
Thanks for that
It is hard to solve this as the problem could be in a number of places. Could you email me a copy of the workbook, so I can look at the code and attempt a fix?
my email is sw@stl-training.co.uk
Thanks
Stephen
