Convert Several UsedRanges into Tables

Dear Experts:

below code converts a defined range (B2:D8) into a table (using ListObject).
The code works just fine.

Is it possible to expand the macro to automatically convert several ranges into tables?
Naming convention is: Table1, Table2, Table3, etc.

The Ranges are not to be hard-coded but the code should detect these 'used ranges' automatically. Is this possible?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$2:$D$8"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Try this:
Sub CreateSalesTables()
   Dim rngFound As Range
   Dim strAddy As String
   Dim lngCounter As Long
   Set rngFound = ActiveSheet.UsedRange.Find(what:="Sales", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
   If Not rngFound Is Nothing Then
      lngCounter = 1
      strAddy = rngFound.Address
      Do
         CreateTable rngFound.CurrentRegion, "Table" & lngCounter
         lngCounter = lngCounter + 1
         Set rngFound = ActiveSheet.UsedRange.FindNext(rngFound)
      Loop While rngFound.Address <> strAddy
   End If
End Sub

Sub CreateTable(rng As Range, strName As String)
    With rng.Worksheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
      .Name = strName
        'No go in 2003
      .TableStyle = "TableStyleLight2"
   End With

End Sub

Open in new window

0
 
Rory ArchibaldCommented:
"the code should detect these 'used ranges' automatically. Is this possible?"

Not without more information than that. (otherwise it would be magic, not VBA) :)
0
 
Kannan KConnect With a Mentor Manager - EngineeringCommented:
Hi,

rorya comment is absolutely right, But you can make somehow semi dynamic :)

before calling this function you can get parameter for cell ranges using inputbox and do the table formatting

KK,
Sub testmacro()
Dim inCellRange As String

inCellRange = InputBox("Enter the Cell Range", "Cell Range")
Call CreateTable(inCellRange)

End Sub

Sub CreateTable(getCellRange As String)
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(getCellRange), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Andreas HermleTeam leaderAuthor Commented:
Dear rorya, dear KK,

ok, I understand.

KK: works great, thank you very much. Nevertheless I would like to give this 'automatic range detection' a new chance.

Could the code be adjusted under the condition that all data lists/ranges to be converted into tables all have the same MAKE-UP, i.e. the first cell of the the tables (cell(1,1)) always have got 'Sales' as value (String).

If I do a search operation for the string 'Sales' and then press Ctrl+Shift+Right Arrow + Down Arrow, the whole table gets selected.

Doing above operation repeatedly would result in all the tables get selected.

I hope you know what I mean.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi rorya:

this is great coding. Thank you very much for your superb and professional help. It is superfast, works like a charm.

Thank you again. Rorya, I am not quite sure how I should split the points: 75% for you and 25% for KK, I suppose since he also came up with a working solution.

I' ll think about it.

Regards, andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi rorya:

there is something else. I will award the points on this question and then put a new one right away similiar to this. The code may have to be adjusted only slightly.

Instead of creating ListObjects the 'rngFound.CurrentRegion' should just receive a name (named range) with the following name convention: range1, range2, range3 etc.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Again, thank you very much for your superb and swift help. I really appreciate it.

Regards, Andreas
0
 
Rory ArchibaldCommented:
No need - it's a small tweak:
Sub CreateSalesTables()
   Dim rngFound As Range
   Dim strAddy As String
   Dim lngCounter As Long
   Set rngFound = ActiveSheet.UsedRange.Find(what:="Sales", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
   If Not rngFound Is Nothing Then
      lngCounter = 1
      strAddy = rngFound.Address
      Do
         rngFound.CurrentRegion.Name = "Range" & lngCounter
         lngCounter = lngCounter + 1
         Set rngFound = ActiveSheet.UsedRange.FindNext(rngFound)
      Loop While rngFound.Address <> strAddy
   End If
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.