Looping through different size tables Excel VBA

Question

Previous question was answered providing me the foundation of this Loop.

VBA Excel - Loop through worksheet creating tables

However, I ran into an issue where I may have a table header with no data in the line right under it. In this case I simply want to make a table with just the header.

I have tried this code-simply assigning the row below rngStart as oneDown. And then creating an if/then to check if len(oneDown) is > 0.

`Dim ws As Worksheet
Set ws = ActiveSheet

With ws

'find last row of data in column A
Dim lRow As Long
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
Dim rngStart As Range
Set rngStart = .Range("A3")

'set counter variable for naming tables
Dim i As Long
i = i + 1
Dim oneDown As Long
Set oneDown =rngStart.Offset(1)

Do

if Len(oneDown) > 0 Then
    'create table range
    Set rngTable = .Range(rngStart.End(xlToRight),rngStart.End(xlDown))
    'create table
    .ListObjects.Add(xlSrcRange, rngTable.Resize(rngTable.Rows.Count, rngStart.End(xlToRight).Column), , xlYes).Name = "Table" & i
    'set style
    .ListObjects("Table" & i).TableStyle = "TableStyleLight9"
    'find next table range start
    Set rngStart = rngTable.End(xlDown).Offset(2)
 Else
'create table range
    Set rngTable = .Range(rngStart.End(xlToRight))
    'create table
    .ListObjects.Add(xlSrcRange, rngTable.Resize(rngTable.Rows.Count, rngStart.End(xlToRight).Column), , xlYes).Name = "Table" & i
    .ListObjects("Table" & i).TableStyle = "TableStyleLight9"
    Set rngStart = rngTable.End(xlDown).Offset(2)

End If
    i = i + 1

Loop Until rngStart.Row > lRow

End With`

I'm getting the same results with my data as if I didn't have the if/then in place.

@Silva thanks for the tip but that did not work either. Does everything look right with my if/then logic? — Ben Simmons
– Ben Simmons, Commented Feb 5, 2016 at 5:53
Are your data really in a Table or just a Range? If it is a Table, it's a lot easier. — PatricK
– PatricK, Commented Feb 5, 2016 at 6:18
@PatricK Before this code is applied they are in a range- this code is to go through and create tables. Original Question outlining requirement is: stackoverflow.com/questions/35216879/… — Ben Simmons
– Ben Simmons, Commented Feb 5, 2016 at 6:32

Jonathan · Accepted Answer · 2016-02-06 03:51:29Z

1

I had to change part of your code but this worked with I test it so give it a try:

  Dim ws As Worksheet
    Set ws = ActiveSheet

    With ws

    'find last row of data in column A
    Dim lRow As Long
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Dim rngStart As Range
    Set rngStart = .Range("A3")

    'set counter variable for naming tables
    Dim i As Long
    i = i + 1

    Do

Dim oneDown As String
 oneDown = rngStart.Offset(1)

'Proceed to next cell if rngstart is empty
If rngStart.Value = "" Then
   Set rngStart = rngStart.Offset(1)
ElseIf Len(oneDown) > 0 Then
    'create table range
    Set rngtable = .Range(rngStart.End(xlToRight), rngStart.End(xlDown))
    'create table
    .ListObjects.Add(xlSrcRange, rngtable.Resize(rngtable.Rows.Count, rngStart.End(xlToRight).Column), , xlYes).Name = "Table" & i
    'set style
    .ListObjects("Table" & i).TableStyle = "TableStyleLight9"
    'find next table range start
    Set rngStart = rngtable.End(xlDown).Offset(1)
     i = i + 1
 Else
'create table range
    Set rngtable = .Range(rngStart.End(xlToRight), rngStart)
    'create table
    .ListObjects.Add(xlSrcRange, rngtable.Resize(rngtable.Rows.Count, rngStart.End(xlToRight).Column), , xlYes).Name = "Table" & i
    .ListObjects("Table" & i).TableStyle = "TableStyleLight9"
    Set rngStart = rngtable.End(xlDown).Offset(1)
     i = i + 1
End If


    Loop Until rngStart.Row > lRow

    End With

edited Feb 6, 2016 at 3:51

answered Feb 5, 2016 at 14:33

Jonathan

6631 gold badge10 silver badges13 bronze badges

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

4 Comments

Jonathan Over a year ago

I updated the answer and modified your code a bit. It worked when I tested it so give it a try and let me know if you have any issues.

Ben Simmons Over a year ago

It works except when there isn't two spaces between desired Table headers. I've tried deleting all blank rows and then inserting a row above every row that has "CUSTOMER_" in Column A. Now, If I could just modify the rngStart to only start on those rows I would be successful.

Jonathan Over a year ago

I updated it to account for multiple or single blank rows between tables. Should resolve that issue without the need for the customer_ identifier.

Ben Simmons Over a year ago

@Jonathan- works like a charm! Thanks for the solution!

Collectives™ on Stack Overflow

Looping through different size tables Excel VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related