-
May 11th, 2012, 08:53 AM
#1
Thread Starter
New Member
Automatically deleting values
Hi Everyone I wonder if someone can help me.
On excel I want to automatically delete all rows if both columns N and O have the value 0.
Then I would like colomns A B D G J K L M P Q deleted.
Which is the best way to approach this?
Many thanks in advance
-
May 11th, 2012, 09:04 AM
#2
Re: Automatically deleting values
welcome to the forum,
it's better post what u hav tried so far, anyway try something like this
Code:
Dim i As Long, DelRng As Range
For i = 1 To 100
If Cells(i, "N").Value = 0 And Cells(i, "O").Value = 0 Then
Set DelRng = Union(Cells(i, "A"), Cells(i, "B"), Cells(i, "D")) 'add ur cells
DelRng.ClearContents
End If
Next
-
May 11th, 2012, 10:45 AM
#3
Thread Starter
New Member
Re: Automatically deleting values
Thanks for your post,
I have tried this code but it doesn't work.
The contents of the colums are cleared but the headings are not.
Also the rows N and O still have have the values of 0 in them.
Any other suggestions,
I am very new to VB as i'm a data visualizer, so any help will be welcome
Thanks
-
May 11th, 2012, 11:09 AM
#4
Re: Automatically deleting values
i just posted a sample code, not the perfect as u need, it just loop rows 1 to 100, u can find the last used row in column N like this
Code:
Dim LastRow as Integer
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
then put the lastrow in loop
Code:
For i = 1 To LastRow
-
May 14th, 2012, 07:42 AM
#5
Thread Starter
New Member
Re: Automatically deleting values
Still really struggling with this code like a nube.....
Thanks for your help so far
So let me try and re explain what I want to achieve,
I want to delete entire Columns A B D G J K L M P Q
and then delete entire rows when columns N and O both present the value 0.
And so far this is what I have and its not working out for me.
Dim i As Long, DelRng As Range
For i = 1 To LastRow
If Cells(i, "N").Value = 0 And Cells(i, "O").Value = 0 Then
Set DelRng = Union(Cells(i, "A"), Cells(i, "B"), Cells(i, "D"), Cells(i, "G"), Cells(i, "J"), Cells(i, "K"), Cells(i, "L"), Cells(i, "M"), Cells(i, "P"), Cells(i, "Q"))'
DelRng.ClearContents
Dim LastRow as Integer
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
End If
Next
what am I doing wrong?
thanks in advance
-
May 14th, 2012, 07:59 AM
#6
Re: Automatically deleting values
want to delete entire column or entire row?
-
May 14th, 2012, 08:19 AM
#7
Thread Starter
New Member
Re: Automatically deleting values
delete entire columns of the coulumns i've mentioned ive mentioned.
then delete entire Rows...only when Columns N and O contain 0.
Does that make sense?
thanks
-
May 14th, 2012, 08:23 AM
#8
Re: Automatically deleting values
to delete entire column D, try this
Code:
Cells(1, "D").EntireColumn.ClearContents
'or
Range("D:D").ClearContents
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|