Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro with variable rows
Range("A2:J" & ActiveSheet.Cells.End(xlDown).Row).select
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I autodelete rows in a macro on a variable dataset? | Excel Discussion (Misc queries) | |||
Macro for variable rows (fruit flies) | Excel Discussion (Misc queries) | |||
Macro for copying a value to a variable no. of rows | Excel Discussion (Misc queries) | |||
sum and variable rows | Excel Worksheet Functions | |||
Selecting rows with variable | Excel Programming |