Clear contents from various rows

[VegasNath]

Code: Select all

    Set ws = Sheets("Detail")
    iRow = ws.Range("B31").End(xlUp).Row
    ws.Range("B" & iRow & ":IV" & iRow).ClearContents

If iRow = 8, how can I also clear the contents of rows 39, 70, 101, 132, 163, 198

Similar, If iRow = 9, how can I also clear the contents of rows 40, 71, 102, 133, 164, 199

[HansV]

Are you sure about 198 - there's an interval of 31 between the previous values, so I'd expect the last one to be 194.

[VegasNath]

198 is correct Hans, there is an extra 4 row section.

[HansV]

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

[VegasNath]

Perfect,

[VegasNath]

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

Hans,
How can I adjust this to copy the last row in each section down a row?
This is what I have for the top part:

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))

[HansV]

Try

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

[VegasNath]

Thanks for the reply Hans. I have edited the code so that it lines up with my existing declerations. This is turning into a monster of a procedure. I have:

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

Have i done something wrong? I get RTE 38, object doesn't support this property or method.

[HansV]

wb2.Range(...) is not valid because wb2 is not a worksheet but a workbook. It should be wb2.Sheets("Detail").Range(...) throughout, not just the first time.

[VegasNath]

Cheers Hans, I should have spotted that one!