Data is inserted by a server into a template sheet. My
code copies filtered rows to new sheets and creates
PivotTables. This works fine as long as all 20 columns
contain data.

However, if only col 1 - n have data then an error
message says something like "more than one row is
needed". (Col headings on row one are always filled).

So my question is: how can I replace "20" in the code
below with the number of the last col used for data.
(Cols are always filled left to right).

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 20).End
(xlUp))

Thanks!
Ulf

Ulf

Something along these lines perhaps:

Sub test()
Dim Rng As Range

Set Rng = Range(Cells(1, 1), _
Cells(Rows.Count, Cells(1, 256).End(xlToLeft).Column).End(xlUp))

Rng.Select

End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"ulfb" skrev i en meddelelse
...
Data is inserted by a server into a template sheet. My
code copies filtered rows to new sheets and creates
PivotTables. This works fine as long as all 20 columns
contain data.

However, if only col 1 - n have data then an error
message says something like "more than one row is
needed". (Col headings on row one are always filled).

So my question is: how can I replace "20" in the code
below with the number of the last col used for data.
(Cols are always filled left to right).

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 20).End
(xlUp))

Thanks!
Ulf