Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Delete row based on multiple columns of data
I have multiple columns of data that I am currently moving from one
workbook to another. After it is moved, I want to delete all of the rows that are blank in columns J,M,S, and V. I can not think of the code to do this. Thanks, Jay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Delete row based on multiple columns of data
Hi
Try this for the activesheet Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 If Application.CountA(.Cells(Lrow, 1).Range("J1,M1,S1,V1")) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the first 5 cells in the row are empty Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub See also my code page http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "jlclyde" wrote in message ... I have multiple columns of data that I am currently moving from one workbook to another. After it is moved, I want to delete all of the rows that are blank in columns J,M,S, and V. I can not think of the code to do this. Thanks, Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Delete row based on multiple columns of data
Remove the text
'This will delete the row if the first 5 cells in the row are empty This is from another example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Try this for the activesheet Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 If Application.CountA(.Cells(Lrow, 1).Range("J1,M1,S1,V1")) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the first 5 cells in the row are empty Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub See also my code page http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "jlclyde" wrote in message ... I have multiple columns of data that I am currently moving from one workbook to another. After it is moved, I want to delete all of the rows that are blank in columns J,M,S, and V. I can not think of the code to do this. Thanks, Jay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Delete row based on multiple columns of data
something like this assumes col A is the longest col
Sub delrowifblanks() 'assumes min 2 characters in cell For i = Cells(Rows.Count, "a").End(xlUp).Row To 17 Step -1 If Len(Cells(i, "j")) < 2 And _ Len(Cells(i, "m")) < 2 And _ Len(Cells(i, "s")) < 2 And _ Len(Cells(i, "v")) < 2 Then Rows(i).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jlclyde" wrote in message ... I have multiple columns of data that I am currently moving from one workbook to another. After it is moved, I want to delete all of the rows that are blank in columns J,M,S, and V. I can not think of the code to do this. Thanks, Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of Data Based on Multiple Columns | Excel Worksheet Functions | |||
Filter data based on multiple columns | New Users to Excel | |||
Retreive data from another worksheet based on multiple columns | Excel Discussion (Misc queries) | |||
Retreive data from another worksheet based on multiple columns | New Users to Excel | |||
Delete rows based on multiple criterias | Excel Discussion (Misc queries) |