98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Deleting rows of Data
Deleting rows of Data
Resolved · Urgent Priority · Version 2003
Mon 22 Mar 2010, 14:20
Edited on Mon 22 Mar 2010, 15:08Delegate Rebecca said...
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
Fri 26 Mar 2010, 07:39Trainer Stephen said...
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
Fri 26 Mar 2010, 12:13Delegate Rebecca said...
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
Tue 6 Apr 2010, 15:15
Edited on Tue 6 Apr 2010, 15:15Trainer Stephen said...
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
Fri 16 Apr 2010, 16:29Delegate Rebecca said...
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?
Wed 21 Apr 2010, 11:20Trainer Stephen said...
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
Tue 13 Apr 2010: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Checking formulas with multiple operatorsWhen dealing with formulas containing more than one operator (+, -, /, *), Excel follow standard BEDMAS order of operation rules. These rules specify the order that calculations will be performed in, regardless of how the formula reads left to right: |
