Reposition Table

John is a self-proclaimed amateur and wants your advice. The purpose of this code is to find the table that starts somewhere in the first 50 rows and columns and reposition it so that it starts (top-left cell) in B5. I’m going to assume that there is nothing else on the worksheet except this table. John can correct me if I’m wrong.

For a = 1 To 50
For b = 1 To 50
If Not Cells(a, b) = "” And Flag = 1 Then
Do While Cells(1, b) = "”
'Bring the data from wherever it is up to Row 1
Rows("1:1").Delete shift:=xlUp
' Push data down from Row 1 to Row 5
For k = 1 To 4
Rows("1:1").Insert shift:=xlDown
Next k
'Delete cells/shift left until the data comes into Column A
Do While Cells(5, 1) = "”
Columns("A:A”).Delete shift:=xlLeft
Flag = 0
End If
Next b
Next a

5 thoughts on “Reposition Table

  1. Dick,

    Correct. For conversation sake, this table is 10 columns wide and 200 rows long.

    The object would be to come up with the most efficient code to put the upper left cell of table data in B5.

    Thanks, John

  2. Here’s my shot at it. A few assumptions: table must be contiguous and no other data can exist on the worksheet.

  3. If you create a worksheet and paste a table in it, this will work. The risk is that the worksheet isn’t new and has some remnants of a previous used range.

    Sub RepoTable()

        With ActiveSheet
            .UsedRange.Cut .Range(“b5?)
        End With

    End Sub

  4. This is my suggestion:

    ‘ task: move table with unknown location in range (1,1)-(maxrow,maxcol)
    ‘ to new location with upper-left corner given as newupperleft
    ‘ solution: locate range of table and cut and paste it to its new location

    ‘ parameters
    maxrow = 50
    maxcol = 50
    newupperleft = “B5?

    ‘ scan the search area till the upper-left corner is found, i.e. toprow and leftcol
    toprow = 0
    toprow = toprow + 1
    leftcol = 0
    leftcol = leftcol + 1
    Loop Until leftcol = maxcol Or Cells(toprow, leftcol) <> “”
    Loop While Cells(toprow, leftcol) = “” And toprow < maxrow

    If Cells(toprow, leftcol) = “” Then
    MsgBox “No table found”
    Exit Sub
    End If

    ‘ scan right till we find rightcol
    rightcol = leftcol
    rightcol = rightcol + 1
    Loop While Cells(toprow, rightcol) <> “”
    rightcol = rightcol – 1

    ‘ scan down till we find the bottomrow
    bottomrow = toprow
    bottomrow = bottomrow + 1
    Loop While Cells(bottomrow, leftcol) <> “”
    bottomrow = bottomrow – 1

    ‘ cut and paste the table to its new location
    Range(Cells(toprow, leftcol), Cells(bottomrow, rightcol)).Select

    End Sub

    Which of the suggested solutions is the best? It depends on the criteria used. I like mine because I understand it. :)
    Dick, thanks for this spot for code critique!

  5. Well, I just tried Dick’s because it was the shortest. From a code perspective, less is better, IMHO.

    As for the other suggestions (including mine) I’m not sure which would work the fastest.

    One issue with mine is that it would not work with data pasted outside of the 50,50 limit.

    Thanks for playing!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.