How do I autodelete rows in a macro on a variable dataset?
 

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.

How do I autodelete rows in a macro on a variable dataset?
 

If the rows are blank, select any column and F5SpecialBlanksOK

EditDeleteEntire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

On Wed, 28 Jan 2009 10:08:02 -0800, bimpnottin_noregard
wrote:

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.

How do I autodelete rows in a macro on a variable dataset?
 

The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.

"Gord Dibben" wrote:

If the rows are blank, select any column and F5SpecialBlanksOK

EditDeleteEntire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

On Wed, 28 Jan 2009 10:08:02 -0800, bimpnottin_noregard
wrote:

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.

How do I autodelete rows in a macro on a variable dataset?
 

Select the invoice column and do the F5Special.

You can record a macro at same time.

Or change the "A" to the column in question and run this.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord

On Wed, 28 Jan 2009 10:50:01 -0800, bimpnottin_noregard
wrote:

The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.

"Gord Dibben" wrote:

If the rows are blank, select any column and F5SpecialBlanksOK

EditDeleteEntire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

On Wed, 28 Jan 2009 10:08:02 -0800, bimpnottin_noregard
wrote:

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.

How do I autodelete rows in a macro on a variable dataset?
 

When I do the F5 Special, I can choose blanks, but what would I need to do
after that.

I've been trying the macro, and everything looks right, but I don't know if
it's not working because I'm just trying to record it as a sub macro to test
it, or if I'm doing something else wrong. I changed the "A" to "invoice" to
match my column heading.

"Gord Dibben" wrote:

Select the invoice column and do the F5Special.

You can record a macro at same time.

Or change the "A" to the column in question and run this.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord

On Wed, 28 Jan 2009 10:50:01 -0800, bimpnottin_noregard
wrote:

The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.

"Gord Dibben" wrote:

If the rows are blank, select any column and F5SpecialBlanksOK

EditDeleteEntire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

On Wed, 28 Jan 2009 10:08:02 -0800, bimpnottin_noregard
wrote:

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.

How do I autodelete rows in a macro on a variable dataset?
 

Change the value to the column header that you see at the top--not the headers
that you've typed in.

It can be A through IV (in xl2003 and below).

And Gord's post had these three steps:
select any column
and F5SpecialBlanksOK
EditDeleteEntire Row.



bimpnottin_noregard wrote:

When I do the F5 Special, I can choose blanks, but what would I need to do
after that.

I've been trying the macro, and everything looks right, but I don't know if
it's not working because I'm just trying to record it as a sub macro to test
it, or if I'm doing something else wrong. I changed the "A" to "invoice" to
match my column heading.

"Gord Dibben" wrote:

Select the invoice column and do the F5Special.

You can record a macro at same time.

Or change the "A" to the column in question and run this.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord

On Wed, 28 Jan 2009 10:50:01 -0800, bimpnottin_noregard
wrote:

The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.

"Gord Dibben" wrote:

If the rows are blank, select any column and F5SpecialBlanksOK

EditDeleteEntire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

On Wed, 28 Jan 2009 10:08:02 -0800, bimpnottin_noregard
wrote:

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.





--

Dave Peterson

How do I autodelete rows in a macro on a variable dataset?
 

Thank you. Both of those now worked. Made me glad that I put what I had
done, so you could fix it. I know enough about macros to create them, but
not always fix them.

"Dave Peterson" wrote:

Change the value to the column header that you see at the top--not the headers
that you've typed in.

It can be A through IV (in xl2003 and below).

And Gord's post had these three steps:
select any column
and F5SpecialBlanksOK
EditDeleteEntire Row.



bimpnottin_noregard wrote:

When I do the F5 Special, I can choose blanks, but what would I need to do
after that.

I've been trying the macro, and everything looks right, but I don't know if
it's not working because I'm just trying to record it as a sub macro to test
it, or if I'm doing something else wrong. I changed the "A" to "invoice" to
match my column heading.

"Gord Dibben" wrote:

Select the invoice column and do the F5Special.

You can record a macro at same time.

Or change the "A" to the column in question and run this.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord

On Wed, 28 Jan 2009 10:50:01 -0800, bimpnottin_noregard
wrote:

The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.

"Gord Dibben" wrote:

If the rows are blank, select any column and F5SpecialBlanksOK

EditDeleteEntire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

On Wed, 28 Jan 2009 10:08:02 -0800, bimpnottin_noregard
wrote:

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.





--

Dave Peterson