macro with variable rows
 

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

macro with variable rows
 

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

macro with variable rows
 

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

macro with variable rows
 

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

"Annelie" wrote in message
...
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