Looking for simpler way to match/sort.

[r.baldwin7960]

I have data on a sheet (sheet3) that pulls from a web query table. This tables first column is a list of names, the rest of the columns contain the data. This information changes daily, and I create reports based off of this information.

On Sheet2 in column B, I have a master list of all the names that would appear in the table. (they only appear if they have data for that day) I then copy the table from Sheet3 to Sheet2, starting in column C.

I use the code below so that I can match/sort the names from the table to the names in the master list. This way I can have the names in the same cells each time so that I can use VLOOKUP() on Sheet1 (which I use for the reports).

I hope I haven't lost you so far...

What I'd like to know is if there is a different way of achieving the same results, or if there is a simpler way of writing the code I have so far.

Thank you again in advance!

Sub Sort()
 
 iRow = 2
 
Do Until IsEmpty(Cells(iRow, 2)) Or IsEmpty(Cells(iRow, 3))
If Cells(iRow, 2) < Cells(iRow, 3) Then
Cells(iRow, 3).Insert xlShiftDown
Cells(iRow, 4).Insert xlShiftDown
Cells(iRow, 5).Insert xlShiftDown
Cells(iRow, 6).Insert xlShiftDown
Cells(iRow, 7).Insert xlShiftDown
Cells(iRow, 8).Insert xlShiftDown
Cells(iRow, 9).Insert xlShiftDown
Cells(iRow, 10).Insert xlShiftDown
ElseIf Cells(iRow, 2) > Cells(iRow, 3) Then
Cells(iRow, 1).Insert xlShiftDown
Cells(iRow, 2).Insert xlShiftDown
End If
iRow = iRow + 1
Loop
End Sub

 

[r.baldwin7960]

Another problem that I am having with this is that the section that looks like:

Cells(iRow, 3).Insert xlShiftDown
Cells(iRow, 4).Insert xlShiftDown
Cells(iRow, 5).Insert xlShiftDown
Cells(iRow, 6).Insert xlShiftDown
Cells(iRow, 7).Insert xlShiftDown
Cells(iRow, 8).Insert xlShiftDown
Cells(iRow, 9).Insert xlShiftDown
Cells(iRow, 10).Insert xlShiftDown

....actually continues on to Cells(iRow, 87).Insert........

This makes for a very long block of code. Is there any way to make this happen in one line?

 

[Peter_SSs]

Another problem that I am having with this is that the section that looks like:

Cells(iRow, 3).Insert xlShiftDown
Cells(iRow, 4).Insert xlShiftDown
Cells(iRow, 5).Insert xlShiftDown
Cells(iRow, 6).Insert xlShiftDown
Cells(iRow, 7).Insert xlShiftDown
Cells(iRow, 8).Insert xlShiftDown
Cells(iRow, 9).Insert xlShiftDown
Cells(iRow, 10).Insert xlShiftDown

....actually continues on to Cells(iRow, 87).Insert........

This makes for a very long block of code. Is there any way to make this happen in one line?

Try

Cells(iRow, 3).Resize(, 85).Insert xlShiftDown

 

[r.baldwin7960]

This worked perfectly, Thank you!