Thread: Specify range based on cell contents?

I'm trying to create a macro that copies and sorts a table in 2 different places. I recorded a macro that did the trick at first but have come to the realization that since the ranges i recorded are static, than when i move the tables up or down one row they stop working.

I therefore created 5 boxes which include the needed info to vary the range selection in the macro:
Row Orig. Table Begins
Column Orig Table Begins
Last Row of 1st table
Starting Column of 2nd table
Starting Column of 3rd table

I figured that if i had the data in these cells i could use them as a kind of variable range selection method that the macro would pull off of, but i'm unsure of how to incorporate them.

This is the code with the static ranges:

Sub Sort_Returns()

    Range("AN8:AW8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Range("AY8:BH8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Range("AC8:AL8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-36
    Range("AN8").Select
    ActiveSheet.Paste
    
    Range("AY8").Select
    ActiveSheet.Paste
    
    Range("AN8:AO8").Select
    
    With [Months_Indv_returns_BIF]
.Sort key1:=.Columns(2), Header:=xlNo
End With
    

    Range("AP8:AW8").Select
    Range(Selection, Selection.End(xlDown)).Select
Dim Cols As Range, i As Integer
Set Cols = Selection
For i = 1 To Cols.Columns.Count
Cols.Columns(i).Sort key1:=Cols.Columns(i).Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next i


    With [Months_Pegged]
.Sort key1:=.Columns(2), Header:=xlNo
End With


End Sub

Any ideas on how to make it possible to resize the arrays/ranges when something shifts?

, than when i move the tables up or down one row they stop working.

This is not clear. let us take an example . the data is like this with A1 having column heading and data from A2 down upto A10


h1
20
26
95
29
62
6
38
80
54

now you select a table from A3 to A9 from the above range and you want to sort .
another time you want to sort from A2 to A8. in this case do you want to sort with already sorted data or you want go back to original data and then select and sort.

to sort a selected table in a range of data is first select and sort which can be done manually. if you want a macro why dont use this code statement

Selection.Sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo

this appears to be trivial and so I think I have not understood your problem.

Hi.

Can anyone please help.

Here's my code:

Sub transpose()
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:B6").Select
Application.CutCopyMode = False
Selection.Copy
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
End Sub

It is selecting the same number (6) of cells. I hope that it will select the range depending upon the number of cells with contents.

I have attached a excel screenshot for better understanding.

Please help.

Thank you.