Array macro to clear rows

[VegasNath]

Hi,
I have 3 identical sheets, detail 1, detail 2 & detail 3. Each sheet has 6 sections that end on rows 32, 64, 96, 128, 160 & 192. I need to find the last used row in each section (using column b) and clear the row contents. Is it possible to achieve this in one array?

TIA.

[HansV]

Does this do what you want (test on a copy first)?

Code: Select all

Sub ClearRows()
  Dim i As Long
  Dim j As Long
  Dim r As Long
  For i = 1 To 3
    With Worksheets("Detail " & i)
      For j = 1 To 6
        r = 32 * j
        Do While .Range("B" & r) = "" And r >= 32 * j - 31
          r = r - 1
        Loop
        If r >= 32 * j - 31 Then
          .Range("B" & r).EntireRow.ClearContents
        End If
      Next j
    End With
  Next i
End Sub

[VegasNath]

Great, Thankyou Hans.

[Rick Rothstein]

Assuming there are no blank cells within the data within each group (that is, the only blank cells in each group occur after the last data row in that group, then here is another way to do what you want that involves slightly less looping than Hans' method...

Code: Select all

Sub DeleteLastGroupRows()
  Dim LastUsedRangeRow As Long, A As Range, C As Range, WS As Worksheet
  Const SheetNames As String = "Detail 1,Detail 2,Detail 3"   ' <<--- Note, there cannot be any spaces around the commas
  Const Groups As String = "B1:B32,B33:B64,B65:B96,B97:B128,B129:B160,B161:B192"
  On Error Resume Next
  For Each WS In Sheets(Split(SheetNames, ","))
    For Each A In WS.Range(Groups).Areas
      Set C = A.SpecialCells(xlCellTypeConstants)
      If Not C Is Nothing Then C(C.Count).EntireRow.ClearContents
    Next
  Next
  On Error GoTo 0
End Sub

[Rick Rothstein]

And to follow up on my last posting, here is the code to use if there could be blank cells scattered about within the data in each group...

Code: Select all

Sub DeleteLastGroupRows()
  Dim LastUsedRangeRow As Long, A As Range, C As Range, WS As Worksheet
  Const SheetNames As String = "Sheet2,Sheet5,Sheet6"
  Const Groups As String = "B1:B32,B33:B64,B65:B96,B97:B128,B129:B160,B161:B192"
  On Error Resume Next
  For Each WS In Sheets(Split(SheetNames, ","))
    For Each A In WS.Range(Groups).Areas
      Set C = A.SpecialCells(xlCellTypeConstants)
      If Not C Is Nothing Then C.Areas(C.Areas.Count)(C.Areas(C.Areas.Count).Rows.Count).EntireRow.ClearContents
    Next
  Next
  On Error GoTo 0
End Sub

[VegasNath]

Thankyou for the alternative approach Rick, it's always good to have options!

[Rick Rothstein]

Thankyou for the alternative approach Rick, it's always good to have options!

You are quite welcome, of course. I did want to point out one thing about my approach in case it is not obvious to those following this thread... it is completely independent of the layout being used for the groups themselves. If, for example, you decided at a later date to increase (or decrease) the number of data rows within each group, or to put borders around the groups and then separate these groups with more empty rows between them, or to move the groups from a column presentation to a row presentation (with the individual groups still listing their data by rows, of course), etc., the code I posted can be used, as is, with only one simple change. All you would need to do is change the range assignments in this statement to reflect the new locations of each group (the rest of the code would adapt to this change)...

Code: Select all

  Const Groups As String = "B1:B32,B33:B64,B65:B96,B97:B128,B129:B160,B161:B192"