Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range in Macro to change after rows inserted
Hi
I use the following macro (copied from this group) When I insert rows above the range, I need the range to adjust accordingly. I have tried adapting similar answers found here by naming the range but cannot get it to work. Any help would be appreciated. Private Sub CommandButton15_Click() Dim C As Range With ActiveSheet.Range("j69:j127") Do Set C = .Find("", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If C Is Nothing Then Exit Do C.EntireRow.Hidden = True Loop End With End Sub Thanks Malcolm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range in Macro to change after rows inserted
you can use some project library objects to access and modify the paticular
parts of codes. but there is a possibility that this kind of code will get caught by Antivirus softwares as a false positive. "wizardmalcolm" wrote: Hi I use the following macro (copied from this group) When I insert rows above the range, I need the range to adjust accordingly. I have tried adapting similar answers found here by naming the range but cannot get it to work. Any help would be appreciated. Private Sub CommandButton15_Click() Dim C As Range With ActiveSheet.Range("j69:j127") Do Set C = .Find("", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If C Is Nothing Then Exit Do C.EntireRow.Hidden = True Loop End With End Sub Thanks Malcolm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range in Macro to change after rows inserted
I'm not 100% sure of what you mean. your column of data starts at row 69
Do you mean there may be data at 68 and 67 etc so i have J69:J127 range-named as test on my sheet. The code checks if the cell above J69, ie J68 is empty. If not, the range is extended. This is repeated for each cell above the range Private Sub CommandButton15_Click() Dim C As Range Dim target As Range Set target = ActiveSheet.Range("test") '("j69:j127") Do While target.Offset(-1).Resize(1, 1) < "" And target.Row 1 Set target = target.Offset(-1).Resize(target.Rows.Count + 1) Loop target.Name = "test" With target Do Set C = .Find("", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If C Is Nothing Then Exit Do C.EntireRow.Hidden = True Loop End With End Sub "wizardmalcolm" wrote: Hi I use the following macro (copied from this group) When I insert rows above the range, I need the range to adjust accordingly. I have tried adapting similar answers found here by naming the range but cannot get it to work. Any help would be appreciated. Private Sub CommandButton15_Click() Dim C As Range With ActiveSheet.Range("j69:j127") Do Set C = .Find("", LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If C Is Nothing Then Exit Do C.EntireRow.Hidden = True Loop End With End Sub Thanks Malcolm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Row based on count and copy range to inserted rows | Excel Programming | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
Modify Macro to Include Inserted Rows | Excel Programming | |||
Change colour of cells when content is altered/changed BUT NOT TO INSERTED OR DELETED ROWS | Excel Discussion (Misc queries) | |||
Macro to Number New Rows Inserted Into Table? | Excel Discussion (Misc queries) |