Consulting

Results 1 to 3 of 3

Thread: Auto Group Imported Access Data

  1. #1

    Question Auto Group Imported Access Data

    Hello Everyone,

    I created vba code which permits me to import data from an Access query. Once it has been imported I would like to use vba to auto group the data. I've looked all over and I can't seem to work this out in vba.

    I have attached two files. The import_access_data.xls is with the vba and the end_result.xls includes the grouping format I hope to create using vba coding. Any help is greatly appreciated. Thanks in advance.

  2. #2
    Hi there,

    Here is some code which does the grouping you require. Please note that I haven't bother with the formatting which I am hoping you should be able to do (using the macro recorder if necessary). If not let us know.

    Sub GroupData()
        Dim rCurrentRange As Range
        Dim sGroupName As String
        Dim ii As Integer
    Columns("A:A").Delete Shift:=xlToLeft
        Columns("A:A").Cut
        Columns("D:D").Insert Shift:=xlToRight
    Set rCurrentRange = Cells(2, "A")
    While rCurrentRange.Offset(1, 0) <> ""
            ii = 1
            rCurrentRange.EntireRow.Insert
            sGroupName = rCurrentRange.Value
            rCurrentRange.Offset(-1, 0).Value = sGroupName
            While rCurrentRange.Offset(ii - 1, 0).Value = sGroupName
                rCurrentRange.Offset(ii - 1, 0).Value = ""
                ii = ii + 1
            Wend
            Set rCurrentRange = rCurrentRange.Offset(ii - 1, 0)
        Wend
    End Sub
    Cheers,
    Andrew

  3. #3
    Hairywhiterabbit, thank you for your response and solution. It works like a charm. I admire the clean code. I always enjoy the fact that I am learning something new, everytime I visit this site. Have a great day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •