Problem selecting a whole data matrix

  • Thread starter Thread starter vrzimmerm
  • Start date Start date
V

vrzimmerm

I'm writing a macro to select and copy a large data matrix (excluding
the header row) by using the following VBA coding. Note: the row
containing the column headers is row 5.

Range("A6").Select
Dim rng2 As Range
Set rng2 = Range(ActiveCell, ActiveCell.Offset(-1,
0).End(xlToRight))
Set rng2 = Range(rng2, rng2.End(xlDown))
Selection.Copy

For some reason this code only selects the single cell A6 to be
copied. What am I doing wrong?
 
Can you look at a column to determine how many rows should be copied?

If yes, then maybe you could use:

dim LastRow as long
dim LastCol as long
with worksheets("sheet999")
'This looks at column A
lastrow = .cells(.rows.count,"A").end(xlup).row
'and looks at row 5 to find the last column
lastcol = .cells(5,.columns.count).end(xltoleft).column

.range("a5",.cells(lastrow,lastcol)).copy

'paste somewhere???

end with
 
Assuming no gaps in the first row or column:

Sub tester()

With ActiveSheet.Range("A6")
Range(.End(xlDown), .End(xlToRight)).Copy
End With

End Sub


Tim
 
Back
Top