Code: Select all
Set ws = Sheets("Detail")
iRow = ws.Range("B31").End(xlUp).Row
ws.Range("B" & iRow & ":IV" & iRow).ClearContents
Similar, If iRow = 9, how can I also clear the contents of rows 40, 71, 102, 133, 164, 199
Code: Select all
Set ws = Sheets("Detail")
iRow = ws.Range("B31").End(xlUp).Row
ws.Range("B" & iRow & ":IV" & iRow).ClearContents
Code: Select all
Dim arrList As Variant
Dim n As Variant
arrList = Array(0, 31, 62, 93, 124, 155, 190)
Set ws = Sheets("Detail")
iRow = ws.Range("B31").End(xlUp).Row
For Each n In arrList
ws.Range("B" & (iRow + n) & ":IV" & (iRow + n)).ClearContents
Next n
Hans,HansV wrote:Try this
Code: Select all
Dim arrList As Variant Dim n As Variant arrList = Array(0, 31, 62, 93, 124, 155, 190) Set ws = Sheets("Detail") iRow = ws.Range("B31").End(xlUp).Row For Each n In arrList ws.Range("B" & (iRow + n) & ":IV" & (iRow + n)).ClearContents Next n
Code: Select all
i = wb2.Sheets("Detail").Range("B31").End(xlUp).Row
wb2.Sheets("Detail").Range("B" & i & ":IV" & i).Copy Destination:=(wb2.Sheets("Detail").Range("B" & i & ":IV" & i + 1))
Code: Select all
Dim arrList As Variant
Dim n As Variant
arrList = Array(0, 31, 62, 93, 124, 155, 190)
Set ws = Sheets("Detail")
iRow = ws.Range("B31").End(xlUp).Row
For Each n In arrList
ws.Range("B" & (iRow + n) & ":IV" & (iRow + n)).Copy _
Destination:=ws.Range("B" & (iRow + n + 1) & ":IV" & (iRow + n + 1))
Next n
Code: Select all
arrList = Array(0, 31, 62, 93, 124, 155, 190)
i = wb2.Sheets("Detail").Range("B31").End(xlUp).Row
For Each n In arrList
wb2.Range("B" & (i + n) & ":IV" & (i + n)).Copy Destination:=wb2.Range("B" & (i + n + 1) & ":IV" & (i + n + 1))
Next n