Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows Based on Column Critieria
Basically, I'm trying to delete the entire row in my worksheet if
Columns F-P are all blank. I have this macro written so far, but I'm can't seem to decipher what I'm missing to have it work. Thanks in advance for helping with my novice programming. Sub Gift() With .Cells(Lrow, "F") If Application.CountA(.Range(.Cells(Lrow, "F"), .Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows Based on Column Critieria
On Sep 12, 11:08 am, blackmanofsteel40
wrote: Basically, I'm trying to delete the entire row in my worksheet if Columns F-P are all blank. I have this macro written so far, but I'm can't seem to decipher what I'm missing to have it work. Thanks in advance for helping with my novice programming. Sub Gift() With .Cells(Lrow, "F") If Application.CountA(.Range(.Cells(Lrow, "F"), .Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete End With End Sub Sorry is the full code that I'm having problems with: 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 Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.R ows.Count).Row For Lrow = Lastrow To Firstrow Step -1 If Application.CountA(.Range(.Cells(Lrow, "F"), .Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete Next Lrow ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows Based on Column Critieria
Sub Gift()
Dim r as Range, lrow as Long, i as Long set r = ActiveSheet.UsedRange lrow = r.rows(r.rows.count).row for i = lrow to 1 step -1 With ActiveSheet If Application.CountA(.Range(.Cells(i,"F"), _ .Cells(i, "P"))) = 0 Then .Rows(i).Delete end if End With Next i End Sub -- Regards, Tom Ogilvy "blackmanofsteel40" wrote: Basically, I'm trying to delete the entire row in my worksheet if Columns F-P are all blank. I have this macro written so far, but I'm can't seem to decipher what I'm missing to have it work. Thanks in advance for helping with my novice programming. Sub Gift() With .Cells(Lrow, "F") If Application.CountA(.Range(.Cells(Lrow, "F"), .Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows Based on Column Critieria
Go back to the website and look good at the example code
http://www.rondebruin.nl/delete.htm You delete a lot of the code in the macro See below the macro on my site which part you must replace with the code you want to try. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "blackmanofsteel40" wrote in message ups.com... On Sep 12, 11:08 am, blackmanofsteel40 wrote: Basically, I'm trying to delete the entire row in my worksheet if Columns F-P are all blank. I have this macro written so far, but I'm can't seem to decipher what I'm missing to have it work. Thanks in advance for helping with my novice programming. Sub Gift() With .Cells(Lrow, "F") If Application.CountA(.Range(.Cells(Lrow, "F"), .Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete End With End Sub Sorry is the full code that I'm having problems with: 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 Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.R ows.Count).Row For Lrow = Lastrow To Firstrow Step -1 If Application.CountA(.Range(.Cells(Lrow, "F"), .Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete Next Lrow 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 | |||
how to count entries in one column based on critieria in another c | Excel Discussion (Misc queries) | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting rows based on values in a column | Excel Programming | |||
Deleting rows based on values in a column | Excel Programming | |||
Deleting rows based upon the value in column D | Excel Programming |