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
Loop
' 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
Loop
Flag = 0
End If
Next b
Next a
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
Here’s my shot at it. A few assumptions: table must be contiguous and no other data can exist on the worksheet.
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
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
Do
toprow = toprow + 1
leftcol = 0
Do
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
Do
rightcol = rightcol + 1
Loop While Cells(toprow, rightcol) <> “”
rightcol = rightcol – 1
‘ scan down till we find the bottomrow
bottomrow = toprow
Do
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
Selection.Cut
Range(newupperleft).Select
ActiveSheet.Paste
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!
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!
John