Macro Cell Range reference changes with the addition of New Rows in Active Sheet

[UnitedCloud01]

Hi Everyone

I have a Macro that currently works well until I have to add extra rows into the sheet. When I do this, it necessitates me manually updating the cell range for the column as well as the destination cell for the data that will be retrieved from the column range.

The column has data in it that relates to names of patients that are due for a review. When I press on the button linked to the macro, this data is retrieved, and all the names are listed in the destination cell so clinicians can work through the list.

Ideally, I would like the range to be dynamic so it can accommodate rows being added. I would also like the destination cell to move with the additional rows and for the macro to accommodate this without manual amendments.

The below is the macro

Reminder for Today (Rem Today)

Sub ab_notsonull()
Dim X As String, cell As Range
For Each cell In Range("Z5:Z2100")
If (Len(cell.Value) > "*") Then
X = X & (cell.Value & "" & Chr(10))
End If
Next
Range("C2105").Value = Left$(X, Len(X) - 1)
Range("C2105").WrapText = True
End Sub

Thanks so much for assisting.

Scott

 

[RobP]

maybe this would help:

Sub ab_notsonull()

Dim X As String, cell As Range
Dim last_row As Long

last_row = ActiveSheet.Cells(Rows.Count, 26).End(xlUp).Row '26 = Col Z (26th column)

For Each cell In Range("Z5:Z" & last_row)
    If (Len(cell.Value) > "*") Then
        X = X & (cell.Value & "" & Chr(10))
    End If
Next

Range("C2105").Value = Left$(X, Len(X) - 1)
Range("C2105").WrapText = True

End Sub

sorry, I'm unclear on the

Range("C2105").Value = Left$(X, Len(X) - 1)
Range("C2105").WrapText = True

rows in terms of how they need to be changed ?

 

[RobP]

Possibly if your destination row in col C is always 5 rows after your last row of data in Col Z, then you could use this :

Sub ab_notsonull()

Dim X As String, cell As Range
Dim last_row As Long

last_row = ActiveSheet.Cells(Rows.Count, 26).End(xlUp).Row '26 = Col Z (26th column)

For Each cell In Range("Z5:Z" & last_row)
    If (Len(cell.Value) > "*") Then
        X = X & (cell.Value & "" & Chr(10))
    End If
Next

Range("C" & last_row+5).Value = Left$(X, Len(X) - 1) 'last row of Col Z + 5 rows below ?  (or adjust as necessary ?)
Range("C"& last_row+5).WrapText = True

 

[UnitedCloud01]

Possibly if your destination row in col C is always 5 rows after your last row of data in Col Z, then you could use this :

Sub ab_notsonull()

Dim X As String, cell As Range
Dim last_row As Long

last_row = ActiveSheet.Cells(Rows.Count, 26).End(xlUp).Row '26 = Col Z (26th column)

For Each cell In Range("Z5:Z" & last_row)
    If (Len(cell.Value) > "*") Then
        X = X & (cell.Value & "" & Chr(10))
    End If
Next

Range("C" & last_row+5).Value = Left$(X, Len(X) - 1) 'last row of Col Z + 5 rows below ?  (or adjust as necessary ?)
Range("C"& last_row+5).WrapText = True

Thank you so much!!! This is perfect. Genius. You have saved me many more hours of trying to work out a solution.

 

[RobP]

your welcome.

thanks for the feedback

Rob