macro with variable rows

[Annelie]

I am working with a table which was exported from Quickbooks and I am trying
to prepare it using a macro and then copy it into an access table.

The columns for the data are always the same, the go from A to J, but the
number of rows vary.
A is blank, because I need this blank field for the autonumber field in
access.
I do not know much about macros, so I so I use the build in record the macro
using relative addresses. But somehow, excel always puts some absolute
address into it.

Windows("gltrans.xls").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Range("B1:B2").Select
Selection.EntireRow.Delete
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 9)),
TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 9)),
TrailingMinusNumbers:=True
Range("B2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 7).Range("A1").Select

This macro stops at J2663 - which this week it the last cell
How do I highlight the A2 to J2663 without putting any relative addresses in
it.
Annelie

 

[Guest]

Range("A2:J" & ActiveSheet.Cells.End(xlDown).Row).select

 

[SunTzuComm]

Annelie,

I'm can't tell exactly what type of processing your macro does, but here is the
basic principle of handling an unknown number of rows or columns.

If you select -- that is, highlight -- all the data in your worksheet, the
macro can process all the selected rows and columns this way.

' List the contents of all cells in a selected range:
Dim vntCol As Variant
Dim vntRow As Variant

For Each vntRow In Selection.Rows
For Each vntCol In Selection.Columns
With ActiveSheet.Cells(vntRow.Row, vntCol.Column)
Debug.Print .Value
End With
Next vntCol
Next vntRow

I hope this helps,
Wes

 

[Annelie]

Thanks to both of you, however, I took Chris' one liner and it did the job
for me.
Thanks, Annelie