macro to delete rows

March 5, 2009 at 13:49:28
Specs: Windows Vista
I need help creating a macro to do the following:

Ex, using columns A to F which has information in the rows under it, if there's a zero and/or blank in all columns above delete that row of information


See More: macro to delete rows

Report •


#1
March 5, 2009 at 17:01:13
Will there be text and numbers in a row or will it be all numbers?

Report •

#2
March 5, 2009 at 17:41:41
There is also text in the same row as the numbers.

Example: I want macro to look in column a & b if there's a zero in both, blank in both or zero & blank in both delete the row.

So if there's a number in A or B (exclude zero & blank) keep row

A/B/C/D/E/F/G
0/BLANK/SMITH
4/0/SMITH

1ST row delete because it has a zero and blank


Report •

#3
March 5, 2009 at 18:04:26
Unless I'm missing something, you just changed the requirements.

In your first post you said:

if there's a zero and/or blank in all columns above delete that row

In your second post, you say:

1ST row delete because it has a zero and blank

But the 1st row only has a zero and blank in Columns A and B, not all columns.

I'm confused...


Report •

Related Solutions

#4
March 5, 2009 at 19:01:23
I'm only concerned about the 1st 2 columns, if there's anything after column b it doesn't matter.
I only want the row to stay if the 1st 2 columns have anything but a blank/zero.
Does that make sense?

Report •

#5
March 5, 2009 at 20:14:19
For a macro to keep track of where it is while deleting rows, it has to delete them from the bottom up.

Before it can do that, it has to find the bottom of the range. The first part of this code first determines the longest column. The second part checks columns A and B for combinations of 0 and blanks, starting at the last row and moving up.

I hope that is what you were looking for.

Sub DelBlankZero()
'Find Last Row with Data, could be any Column A:F
 For ColNum = 1 To 6
  NxtLastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
   If NxtLastRow > LastRow Then LastRow = NxtLastRow
 Next
'Check Columns A:B for Combinations
 For DelRow = LastRow To 1 Step -1
  If (Cells(DelRow, 1) = 0 And Cells(DelRow, 2) = 0) Or _
     (Cells(DelRow, 1) = "" And Cells(DelRow, 2) = "") Or _
     (Cells(DelRow, 1) = 0 And Cells(DelRow, 2) = "") Or _
     (Cells(DelRow, 1) = "" And Cells(DelRow, 2) = 0) Then
'Delete Row If Combination Found 
      Rows(DelRow).EntireRow.Delete
   End If
 Next
End Sub


Report •

#6
March 8, 2009 at 12:39:50
I wish I could paste a screen shot of my doc I need help with it's so hard to explain in writing.

I want to keep the following rows there if anything below is in one the the columns listed:

A,B,C,D, E, H & I: any #'s between 1-300
F: any "C's" or "I's"

If it has a zero or blank in all columns A-I it can be deleted.

Hope that makes more sense. The other macro script didn't work.


Report •

#7
March 8, 2009 at 14:18:19
>>I wish I could paste a screen shot of my doc I need help with it's so hard to explain in writing.

Alt & Print Screen then go to Word or whatever and paste with Ctrl & V

Unfortunately you can't post it here.

But you may be able to post it to a blog or website and then post the link.

MIKE


Report •


Ask Question