Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Hi Folks,
I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Here is one way, without a lot of re-write.
For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete .Rows(Lrow - 1).Delete Next Lrow "mooresk257" wrote in message ... Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
For Lrow = LastRow To FirstRow Step -1
If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow-1).resize(2).Delete Next Lrow -- Don Guillett Microsoft MVP Excel SalesAid Software "mooresk257" wrote in message ... Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Don't know what I was thinking. that will delete the wrong row, since you
have already deleted the row with the criteria string. So: For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete .Rows(Lrow).Delete Next Lrow "mooresk257" wrote in message ... Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
This also works:
For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow & ":" & Lrow - 1).Delete Next Lrow "mooresk257" wrote in message ... Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Hi
By before it do you mean the row above, if so Rows(lrow & ":" & lrow - 1).Delete -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "mooresk257" wrote: Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
I'd use:
'not sure what firstrow is here -- not sure if you want to 'use FirstRow or FirstRow - 1 'for example: if FirstRow is 1, then FirstRow - 1 won't work 'start with the row above For Lrow = LastRow - 1 To FirstRow - 1 Step -1 'but look at the row below If .Cells(Lrow + 1 , "A").Value = "Employee Name" Then .Rows(Lrow).resize(2).Delete end if Next Lrow ps. I hate that single line if/then/else (especially with the line continuation character). My eyes sometimes miss it and I hate when that happes! mooresk257 wrote: Hi Folks, I want to delete two rows - one row has the words "Employee Name" in the first column, and the row before it. This for-next loop removes the row with "Employee Name": For Lrow = LastRow To FirstRow Step -1 If .Cells(Lrow, "A").Value = "Employee Name" Then _ .Rows(Lrow).Delete Next Lrow Any suggestions how to delete the row with "Employee Name" and the one before it? Thanks! Scott -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting blank rows for up to 60000 rows of data | Excel Programming | |||
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 | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming |