VBA Ranges criteria

[joes2580]

Ok so here is my question:

In my code I have for example Range("A7:A8527").Select

My problem is, when I import the information every month to run this code, The number of lines can vary.

Can I manipulate the syntax to where it will capture the data in the cells I want no matter how many rows there are? I also want it to end at the end of the data; not go all the way down to the end of the sheet. For Ex. if I do ("A:A") the data will go down the bottom of the sheet.

 

[njimack]

2 suggestions:
Use dynamic ranges - see here: http://www.contextures.com/xlNames01.html#Dynamic
Don't select the range - you don't need to select cells or sheets to perform actions on them

 

[joes2580]

I need to select the range to input formulas; then copy/paste formulas then paste values so I can sort the data.

 

[njimack]

I need to select the range to input formulas; then copy/paste formulas then paste values so I can sort the data.

No you don't. For example...

Sub test()

With Sheets("Sheet1")
    With .Range("A1:A10")
        .Formula = "=SUM(B1:B2)"
        .Formula = .Value
    End With
End With


End Sub