For Loop macro in Excel
For Loop macro in Excel
(OP)
If anyone can help modify this macro, I would greatly appreciated it. Thnks in advance,
The procedure below will format(put a line through) a range in Excel sheet, and it will loop and do the same for every 400 rows below. The problem is the procedure below will loop and format not a RANGE(that I wanted), but the whole ROW. I tried to change the row to range function, but couldn't figure it out.
Leah,
Sub FORMATLINE()
Dim hRows As Long
Const Jump1 As Long = 400
Const LastRow As Long = 18945 'last row to loop
Range("DK21:DO22").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'the loop
For hRows = 21 To LastRow Step Jump1
Rows(CStr(hRows + Jump1)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Next hRows
End Sub
The procedure below will format(put a line through) a range in Excel sheet, and it will loop and do the same for every 400 rows below. The problem is the procedure below will loop and format not a RANGE(that I wanted), but the whole ROW. I tried to change the row to range function, but couldn't figure it out.
Leah,
Sub FORMATLINE()
Dim hRows As Long
Const Jump1 As Long = 400
Const LastRow As Long = 18945 'last row to loop
Range("DK21:DO22").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'the loop
For hRows = 21 To LastRow Step Jump1
Rows(CStr(hRows + Jump1)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Next hRows
End Sub
RE: For Loop macro in Excel
Rows(CStr(hRows + Jump1)).Select
selects by rows
you need some that looks like:
range(cells(hRows + Jump1, 89),cells(hRows + Jump1,93)).select
where 89 should correspond to your column DK and 93 should correspond to column DO
TTFN