Range in Macro to change after rows inserted

[wizardmalcolm]

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

 

[Sagar]

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.

 

[Patrick Molloy]

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