Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I select a range using a macro?
Via macro, I would like to delete all rows that have a value of one (1) in
column H. All rows that have a value of (1) will be contiguous, meaning they will follow one after the other (they won't be separate by rows in between that do not have a value of (1) in column H); however, where the rows with a value of (1) begin and end will vary each time the macro is used. For example, rows 1 through 10 will not have a value of (1) in column H, but rows 11 through 14 will. The next time the macro is run, it may be that rows one through eight won't have a value of (1) in column H, but rows nine through eleven will. I'd like the macro to identify these rows with a value of (1) in column H, select just those rows, and delete them. Thanks for the help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I select a range using a macro?
Public Sub test()
Dim iLastRow As Long Dim i As Long Dim iStart As Long Dim iEnd As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row For i = 1 To iLastRow + 1 If Cells(i, "H").Value = 1 Then iStart = i Do i = i + 1 Loop Until Cells(i, "H").Value < 1 iEnd = i - 1 Exit For End If Next i If iStart 0 Then Rows(iStart & ":" & iEnd).Delete End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "brettopp" wrote in message ... Via macro, I would like to delete all rows that have a value of one (1) in column H. All rows that have a value of (1) will be contiguous, meaning they will follow one after the other (they won't be separate by rows in between that do not have a value of (1) in column H); however, where the rows with a value of (1) begin and end will vary each time the macro is used. For example, rows 1 through 10 will not have a value of (1) in column H, but rows 11 through 14 will. The next time the macro is run, it may be that rows one through eight won't have a value of (1) in column H, but rows nine through eleven will. I'd like the macro to identify these rows with a value of (1) in column H, select just those rows, and delete them. Thanks for the help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I select a range using a macro?
Sub delete_some_rows()
Dim C As Range With Columns("H") Do Set C = .Find(1, LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If C Is Nothing Then Exit Do C.EntireRow.Delete Loop End With End Sub Gord Dibben MS Excel MVP On Wed, 8 Nov 2006 14:53:02 -0800, brettopp wrote: Via macro, I would like to delete all rows that have a value of one (1) in column H. All rows that have a value of (1) will be contiguous, meaning they will follow one after the other (they won't be separate by rows in between that do not have a value of (1) in column H); however, where the rows with a value of (1) begin and end will vary each time the macro is used. For example, rows 1 through 10 will not have a value of (1) in column H, but rows 11 through 14 will. The next time the macro is run, it may be that rows one through eight won't have a value of (1) in column H, but rows nine through eleven will. I'd like the macro to identify these rows with a value of (1) in column H, select just those rows, and delete them. Thanks for the help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I select a range using a macro?
Bob and Gord, thank you for your replies! I ended up going with Gord's
simply because the coding was shorter. It was exactly what I was looking for. Brett "Gord Dibben" wrote: Sub delete_some_rows() Dim C As Range With Columns("H") Do Set C = .Find(1, LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If C Is Nothing Then Exit Do C.EntireRow.Delete Loop End With End Sub Gord Dibben MS Excel MVP On Wed, 8 Nov 2006 14:53:02 -0800, brettopp wrote: Via macro, I would like to delete all rows that have a value of one (1) in column H. All rows that have a value of (1) will be contiguous, meaning they will follow one after the other (they won't be separate by rows in between that do not have a value of (1) in column H); however, where the rows with a value of (1) begin and end will vary each time the macro is used. For example, rows 1 through 10 will not have a value of (1) in column H, but rows 11 through 14 will. The next time the macro is run, it may be that rows one through eight won't have a value of (1) in column H, but rows nine through eleven will. I'd like the macro to identify these rows with a value of (1) in column H, select just those rows, and delete them. Thanks for the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro help | Excel Discussion (Misc queries) | |||
Range Name affecting speed of macro | Excel Worksheet Functions | |||
Copy/Paste using a macro that identifies occuoied range | Setting up and Configuration of Excel | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions |