Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I ADD ROWS WITH TWO TEXT VARIABLE | Excel Discussion (Misc queries) | |||
Macro for variable rows (fruit flies) | Excel Discussion (Misc queries) | |||
Macro for copying a value to a variable no. of rows | Excel Discussion (Misc queries) | |||
copy down with variable number of rows | Excel Discussion (Misc queries) | |||
sum and variable rows | Excel Worksheet Functions |