PDA

View Full Version : [SOLVED] Auto Group Imported Access Data



brorick
01-07-2005, 10:11 AM
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.

hairywhiterabbit
01-09-2005, 03:09 PM
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

brorick
01-10-2005, 06:52 AM
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!