Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Hi Guys im trying to delete empty rows with a macro using the following vba
however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Which column are you in? See my code below...
'This subroutine will delete an entire row if the value in a certain column, 'in this case column "T", is blank. 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, "T").Value = "" Then Rows(RowNdx).Delete End If Next RowNdx End Sub 'This macro will delete all of the blank rows in the active 'worksheet or in the selection. If the current selection 'covers more than one row, only blank rows in those rows 'will be deleted. Otherwise, all blank rows in the entire 'worksheet will be deleted. The entire row must be blank 'for the row to be deleted. 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 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Hi ryan, some columns have zeros in them and some are blank but i would need
the entire row in that instance deleted, im not sure if your code would work? "ryguy7272" wrote: Which column are you in? See my code below... 'This subroutine will delete an entire row if the value in a certain column, 'in this case column "T", is blank. 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, "T").Value = "" Then Rows(RowNdx).Delete End If Next RowNdx End Sub 'This macro will delete all of the blank rows in the active 'worksheet or in the selection. If the current selection 'covers more than one row, only blank rows in those rows 'will be deleted. Otherwise, all blank rows in the entire 'worksheet will be deleted. The entire row must be blank 'for the row to be deleted. 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 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Well, what is the logic? You have to tell me the logic for me to give you
the right code. A quick solution would be to filter for zeros, or blanks, on the applicable columns, then delete those, unhide, and sort ascending, or descending, again depedning on what is applicable. That's a viable solution if this is a one-off. Code is great if you have to do this over, and over, and over, week after week or day after day. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Scott R" wrote: Hi ryan, some columns have zeros in them and some are blank but i would need the entire row in that instance deleted, im not sure if your code would work? "ryguy7272" wrote: Which column are you in? See my code below... 'This subroutine will delete an entire row if the value in a certain column, 'in this case column "T", is blank. 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, "T").Value = "" Then Rows(RowNdx).Delete End If Next RowNdx End Sub 'This macro will delete all of the blank rows in the active 'worksheet or in the selection. If the current selection 'covers more than one row, only blank rows in those rows 'will be deleted. Otherwise, all blank rows in the entire 'worksheet will be deleted. The entire row must be blank 'for the row to be deleted. 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 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
sorry if im a bit vague i dont use code that often but this will be a high
use workbook so definitely need code.. some rows will have data in them (which i want to keep) and others will only a combination of zeros and blank cells (across columns A to AC) so i want to be able to run a macro that will delete those rows to leave only the rows that have data in them. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Hi Ryan, i figured it out. i can use your code so that if the 1st column is a
zero that row can be deleted.. thansk for your help.. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Awesome! Sometimes it just take a little time for something to click. When
I don't see an obvious answer, I step away from the computer for a bit. When I come back, with fresh eyes, a solution usually presents itself. Sounds like that's what happened here... Ryan--- "Scott R" wrote: Hi Ryan, i figured it out. i can use your code so that if the 1st column is a zero that row can be deleted.. thansk for your help.. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting rows (with zeros) with a macro
Ryan -
I used your macro below and it works perfectly except when I try to use it for cells that contain formulas. Example: Sub Delete Rows() Dim RowNdx As Long Dim LastRow As Long StartRow = 2 LastRow = ActiveSheet.UsedRange.Rows.Count For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "F").Value = "0" Then Rows(RowNdx).Delete End If Next RowNdx End Sub As you can see, I simply added a zero for the Value. My questions is, can I somehow modify this macro to recognize a zero value when that value is not "hard coded" in the cell but obtained via a formula? "ryguy7272" wrote: Which column are you in? See my code below... 'This subroutine will delete an entire row if the value in a certain column, 'in this case column "T", is blank. 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, "T").Value = "" Then Rows(RowNdx).Delete End If Next RowNdx End Sub 'This macro will delete all of the blank rows in the active 'worksheet or in the selection. If the current selection 'covers more than one row, only blank rows in those rows 'will be deleted. Otherwise, all blank rows in the entire 'worksheet will be deleted. The entire row must be blank 'for the row to be deleted. 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 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Scott R" wrote: Hi Guys im trying to delete empty rows with a macro using the following vba however the rows have been copied from another sheet and pasted as values that were from formulas so the 'empty' rows actually contain a lot of "0"'s. the code im using only seems to delete rows with nothing in the row at all. is there a way to tell the macro to delete rows that have zeros in them? Thanks heaps scott Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False StartRow = 2 EndRow = 253 For Lrow = EndRow To StartRow Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).delete 'This will delete the row if the whole row is empty (all columns) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Deleting rows with macro | Excel Worksheet Functions | |||
Macro deleting specified rows | Charts and Charting in Excel |