VBA Loop Through an Array

[Ben AFF]

Hi, please can you help me with the following.
I have worked on a bit of VBA code that loops through and array to change the values of the cells to 1 until last empty cell for all columns in the array.
However its not working as intended. Only if I change the value of J to any of the columns ("A","B",etc..) does work but only changes the values for the specified column.

Can you help with what needs to be changed for the code to loop through all columns and rows? Much thanks.

Sub Loop2()

Dim LastRow As Long, i As Long, j As Long, ws As Worksheet
Set ws = Sheets("Sheet1")

LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

With ws

    For j = 1 To LastCol
               
      For i = 1 To LastRow
     
        .Range(j & i) = 1
     
        Next i

    Next j

End With

End Sub

 

[Jeffrey Mahoney]

You could do it in a loop or all at once. Loop2, I believe, fixes yours. Loop3 is not a loop, but just simply applies the number to all cells you wanted.

Sub Loop2()

  Dim LastRow As Long, i As Long, j As Long, ws As Worksheet
  Set ws = Sheets("Sheet1")

  LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
  lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
  
  With ws
    For j = 1 To lastcol
      For i = 1 To LastRow
        .Cells(i, j) = 1
      Next i
    Next j
  End With

End Sub

Sub Loop3()
  Dim Cel As Range
  Dim Rng As Range
  Dim ws As Worksheet
  
  Set ws = Sheets("Sheet1")

  LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
  lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
  
  With ws
    Set Cel = .Cells(1, 1)
    Set Rng = .Range(Cel, .Cells(LastRow, lastcol))
    Rng.Value = 1
  End With
  
End Sub

 

[Ben AFF]

Much appreciated Sir.

 

[mumps]

Try this one liner:

Sub ChangeValues()
    ActiveSheet.UsedRange.Cells.Value = 1
End Sub

 

[Ben AFF]

Thanks to you as well Mumps.

 

[mumps]

You are very welcome.

 

[Peter_SSs]

Much appreciated Sir.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.


Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.