Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
I have a worksheet has this formula in column A:
=IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ") So it produces blank cell values, but retains the formula. I've tried to use other macros from this discussion group, but they are looking for blank cells, so it doesn't work. I need to create a macro to remove the rows based on the value in any A cell where the value="" Any suggestions? -- Kim Cook Technology Coordinator General Mills |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, 1).Value = "" Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200839 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
This is untested, so if you get an error, post back.
Sub delRws() Dim lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step - 1 If ActiveSheet.Cells(i, 1).Value = "" Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If Next End Sub "1219Cookie" wrote in message ... I have a worksheet has this formula in column A: =IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ") So it produces blank cell values, but retains the formula. I've tried to use other macros from this discussion group, but they are looking for blank cells, so it doesn't work. I need to create a macro to remove the rows based on the value in any A cell where the value="" Any suggestions? -- Kim Cook Technology Coordinator General Mills |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
This deletes an entire row is there is a blank cell in a certain column, in
this case, ColumnA: 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 This deletes an entire row if the entire row is blank: Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub That should cover you in several scenarios! Ryan--- -- RyGuy "JLGWhiz" wrote: This is untested, so if you get an error, post back. Sub delRws() Dim lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step - 1 If ActiveSheet.Cells(i, 1).Value = "" Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If Next End Sub "1219Cookie" wrote in message ... I have a worksheet has this formula in column A: =IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ") So it produces blank cell values, but retains the formula. I've tried to use other macros from this discussion group, but they are looking for blank cells, so it doesn't work. I need to create a macro to remove the rows based on the value in any A cell where the value="" Any suggestions? -- Kim Cook Technology Coordinator General Mills |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
I have tried both of the codes above and neither of them removed the rows.
I'm officially stumped here. -- Kim Cook Technology Coordinator General Mills "ryguy7272" wrote: This deletes an entire row is there is a blank cell in a certain column, in this case, ColumnA: 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 This deletes an entire row if the entire row is blank: Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub That should cover you in several scenarios! Ryan--- -- RyGuy "JLGWhiz" wrote: This is untested, so if you get an error, post back. Sub delRws() Dim lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step - 1 If ActiveSheet.Cells(i, 1).Value = "" Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If Next End Sub "1219Cookie" wrote in message ... I have a worksheet has this formula in column A: =IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ") So it produces blank cell values, but retains the formula. I've tried to use other macros from this discussion group, but they are looking for blank cells, so it doesn't work. I need to create a macro to remove the rows based on the value in any A cell where the value="" Any suggestions? -- Kim Cook Technology Coordinator General Mills |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
I just created a unique filter-in-place and show all rows macros to do what I
needed for now. Thanks for all your suggestions! -- Kim Cook Technology Coordinator General Mills "1219Cookie" wrote: I have tried both of the codes above and neither of them removed the rows. I'm officially stumped here. -- Kim Cook Technology Coordinator General Mills "ryguy7272" wrote: This deletes an entire row is there is a blank cell in a certain column, in this case, ColumnA: 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 This deletes an entire row if the entire row is blank: Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub That should cover you in several scenarios! Ryan--- -- RyGuy "JLGWhiz" wrote: This is untested, so if you get an error, post back. Sub delRws() Dim lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step - 1 If ActiveSheet.Cells(i, 1).Value = "" Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If Next End Sub "1219Cookie" wrote in message ... I have a worksheet has this formula in column A: =IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ") So it produces blank cell values, but retains the formula. I've tried to use other macros from this discussion group, but they are looking for blank cells, so it doesn't work. I need to create a macro to remove the rows based on the value in any A cell where the value="" Any suggestions? -- Kim Cook Technology Coordinator General Mills |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to remove blank rows...
I am stumped also, because I ran the code on my system with formulas in
column A that produced an empty string ("") and it deleted those rows. Perhaps you did not have the sheet with the formulas as the ActiveSheet. To avoid that problem, change "ActiveSheet" in the code to the actual sheet reference in your workbook. "1219Cookie" wrote: I have tried both of the codes above and neither of them removed the rows. I'm officially stumped here. -- Kim Cook Technology Coordinator General Mills "ryguy7272" wrote: This deletes an entire row is there is a blank cell in a certain column, in this case, ColumnA: 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 This deletes an entire row if the entire row is blank: Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub That should cover you in several scenarios! Ryan--- -- RyGuy "JLGWhiz" wrote: This is untested, so if you get an error, post back. Sub delRws() Dim lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) For i = lr To 2 Step - 1 If ActiveSheet.Cells(i, 1).Value = "" Then ActiveSheet.Cells(i, 1).EntireRow.Delete End If Next End Sub "1219Cookie" wrote in message ... I have a worksheet has this formula in column A: =IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ") So it produces blank cell values, but retains the formula. I've tried to use other macros from this discussion group, but they are looking for blank cells, so it doesn't work. I need to create a macro to remove the rows based on the value in any A cell where the value="" Any suggestions? -- Kim Cook Technology Coordinator General Mills |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Automatically remove blank rows | Excel Discussion (Misc queries) | |||
create macro that erases rows having blank cells and | Excel Programming | |||
Need macro to remove blank rows | Excel Programming | |||
How do I create a Macro to sort data and insert blank rows & subto | Excel Worksheet Functions | |||
Can I create a macro to identify and delete blank rows in a range? | Excel Programming |