ExcelBanter - select only used rows and only visible cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - select only used rows and only visible cells (https://www.excelbanter.com/excel-discussion-misc-queries/111814-select-only-used-rows-only-visible-cells.html)

select only used rows and only visible cells

I've used the following macro by Frank Kabel to select only usedrows
but as I'm dealing with subtotals (level 2 so only the subtotals
themselves are visible) I only want to copy the visible rows. How do I
go about doing that?

Before the Copy command I've specified -
Selection.SpecialCells(xlCellTypeVisible).Select - but it still copies
all the used rows not the visible cells only

"Sub copy_used()
Dim source As Worksheet
ActiveSheet.UsedRange.Copy
Set source = Worksheets("Sheet2")
source.Paste Destination:=source.Range("A1")
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany "

select only used rows and only visible cells

I don't see where you specified just the visible cells in your code.

Rokuro kubi wrote:

I've used the following macro by Frank Kabel to select only usedrows
but as I'm dealing with subtotals (level 2 so only the subtotals
themselves are visible) I only want to copy the visible rows. How do I
go about doing that?

Before the Copy command I've specified -
Selection.SpecialCells(xlCellTypeVisible).Select - but it still copies
all the used rows not the visible cells only

"Sub copy_used()
Dim source As Worksheet
ActiveSheet.UsedRange.Copy
Set source = Worksheets("Sheet2")
source.Paste Destination:=source.Range("A1")
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany "

--

Dave Peterson