Macro stop at Last Row

[Lish rb]

Hello,

I'm attempting to write a macro where all populated cells in Column D are Trimmed and changed to Uppercase.
Column D is updated weekly and may contain blank cells.

Ex: 10 total rows in Column D total, but row 8 is blank. (I am new to writing macros so I may be in over my head here)

My current macro is not stopping at the last row. In the above example it should stop at row 10, but it isn't. Below is the Macro. Can anyone assist?

Sub Test()

Set JSheet = Worksheets("Sheet1")
Dim rng As Range
Set rng = [D:D]
Dim LastRow As Long

For Each Cell in rng
Cell.Value = WorksheetFunction.Trim(Cell)
Cell.Value = VBA.UCase(Cell)
Debug.Print Cell.Value

LastRow = JSheet.Cells(Rows.Count, 4).End(xlUp).Row

Next

End Sub

 

[Joe4]

Welcome to the Board!

You are setting it equal to a variable, but then are not doing anything with it.

Sub Test()

Dim JSheet As Worksheet
Dim LastRow As Long
Dim rng As Range

Set JSheet = Worksheets("Sheet1")
LastRow = JSheet.Cells(JSheet.Rows.Count, 4).End(xlUp).Row
Set rng = JSheet.Range("D1:D" & LastRow)

For Each Cell In rng
    Cell.Value = WorksheetFunction.Trim(Cell)
    Cell.Value = VBA.UCase(Cell)
    Debug.Print Cell.Value
Next

End Sub

 

[Lish rb]

Welcome to the Board!

You are setting it equal to a variable, but then are not doing anything with it.

Sub Test()

Dim JSheet As Worksheet
Dim LastRow As Long
Dim rng As Range

Set JSheet = Worksheets("Sheet1")
LastRow = JSheet.Cells(JSheet.Rows.Count, 4).End(xlUp).Row
Set rng = JSheet.Range("D1:D" & LastRow)

For Each Cell In rng
    Cell.Value = WorksheetFunction.Trim(Cell)
    Cell.Value = VBA.UCase(Cell)
    Debug.Print Cell.Value
Next

End Sub

That worked perfectly. Thankyou so much for your help!

 

[Joe4]

You are welcome.
Glad I was able to help!