VBA excel macro to parse blocks of data in excel

Question

I'm brand new to VBA for excel (like a few hours ago new) and not really a programmer, so bear with me.

I have an excel data set, all in one column (column A) that is structured like this:

Data
Data
Data

Data
Data
Data

Data
Data
Data
Data
Data

Data
Data

That is, the data blocks are separated by blank rows, but not at regular intervals. I'm trying to write a macro that will go through the file and Group (the specific excel command) these blocks of data together. So far I have this:

Set firstCell = Worksheets("627").Range("A1")
Set currentCell = Worksheets("627").Range("A1")

Do While Not IsEmpty(firstCell)
    Set firstCell = currentCell

    Do While Not IsEmpty(currentCell)

        Set nextCell = currentCell.Offset(1, 0)

        If IsEmpty(nextCell) Then
        Range("firstCell:currentCell").Select
        Selection.Rows.Group
        Set firstCell = nextCell.Offset(1, 0)

        Else
            Set currentCell = nextCell

        End If


    Loop

Loop

I'm sort of stuck, having particular trouble with the logic of moving to the next block of data and initiating.

Any help would be appreciated!

I'm not sure? I do know that I can manually go through and group the blocks of data... — Davigor
– Davigor, Commented Jun 16, 2016 at 1:31
yeah, I think that I am using a different version or something. Group help specifies pivot table but other pages don't limit it like that. — Rodger
– Rodger, Commented Jun 16, 2016 at 1:32
@Davigor Four answers have been posted. If any of these answers solved your problem then it would be appreciated if you could mark this answer as the "accepted" answer. Feel free to potentially up-vote other answers if any of them helped you resolve your problem. By accepting an answer you are showing that your problem is resolved and does not require more answers / attention from other users. Also, it shows your appreciation for the help provided. — Ralph
– Ralph, Commented Jun 16, 2016 at 9:34

Ralph · Accepted Answer · 2016-06-16 02:08:17Z

1

How about something like this:

Option Explicit

Public Sub tmpTest()

Dim i As Long
Dim lngLastRow As Long

With ThisWorkbook.Worksheets(1)
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = lngLastRow To 1 Step -1
        If .Cells(i, 1).Value2 = vbNullString Then
            .Range(.Cells(i + 1, 1), .Cells(lngLastRow, 1)).EntireRow.Group
            lngLastRow = i - 1
        End If
    Next i
    .Range(.Cells(1, 1), .Cells(lngLastRow, 1)).EntireRow.Group
End With

End Sub

edited Jun 16, 2016 at 2:08

answered Jun 16, 2016 at 1:56

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Davigor Over a year ago

This worked right off the bat... Thanks so much! Now I just gotta figure out how it works...

Rodger · Accepted Answer · 2016-06-16 01:52:02Z

Here ya are. You just need to pull addresses in your range instead of trying to refer to the object. You also need to reset both current and first cell in your if statement.

Sub test()
Set firstCell = Worksheets("test2").Range("A1")
Set currentcell = Worksheets("test2").Range("A1")

Do While Not IsEmpty(firstCell)
    Set firstCell = currentcell

    Do While Not IsEmpty(currentcell)

        Set nextcell = currentcell.Offset(1, 0)
        If IsEmpty(nextcell) Then
          Range(firstCell.Address, currentcell.Address).Select
          Selection.Rows.group
          Set currentcell = nextcell.Offset(1, 0)
          Set firstCell = nextcell.Offset(1, 0)

        Else
            Set currentcell = nextcell

        End If


    Loop

Loop

End Sub

MutjayLee · Accepted Answer · 2016-06-16 02:04:38Z

0

First of all, your code goes wrong when it says

Range("firstCell:currentCell").Select

You are trying to select the range named "firstCell:currentCell" instead of

selecting range from first Cell to currentCell

You should change it to

.Range(firstCell,currentCell).select

Try using below code and see if it does what you want it to do

Dim GROUP_LAST_CELL As Range

With Worksheets("627")

    LAST_ROW = .Range("A" & Rows.Count).End(xlUp).Row

    I = 1

    While I <= LAST_ROW
        Set GROUP_LAST_CELL = .Cells(I, 1).End(xlDown)
        .Range(.Cells(I, 1), GROUP_LAST_CELL).Rows.Group
        I = GROUP_LAST_CELL.Row + 2
    Wend

End With

answered Jun 16, 2016 at 2:04

MutjayLee

5683 silver badges6 bronze badges

Comments

Rohit Nair · Accepted Answer · 2016-06-16 02:23:58Z

0

According to what i understood from the question, i think what you want to do is to loop across all the elements in a particular column, skipping all the blanks.

You can do so by

Calculating the lastrow of the column
Looping across from the first row count to the calculated lastRow count
Applying a condition within the loop to only print the non-empty cells

Code Block

Sub test()

Dim j As Long, lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For j = 1 To lastRow

    If Cells(j, "A").Value <> "" Then
        MsgBox (Cells(j, "A").Value)

     End If
Next j

End Sub

I Hope this helped!

answered Jun 16, 2016 at 2:23

Rohit Nair

6883 gold badges7 silver badges24 bronze badges

Collectives™ on Stack Overflow

VBA excel macro to parse blocks of data in excel

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related