Retrieving the visible records

[KVRajesh]

Hi Everyone,

I'm new to writing code in macro's. I have a requirement where in i have to copy visible rows except headers from one sheet say sheetSS and paste them to other sheet say sheetRD for particular number of iterations.

User can manually filter the records in sheet1. there are 5 columns in the sheet1. Independent of what field user filters, we have to copy all the visible rows to next sheet.

I have used the below code snippet for selecting the visible rows

Set rngCopy = sheetSS.Range("A6", Range("A6").End(xlDown)).Rows.SpecialCells(xlCellTypeVisible)
copyCount = rngCopy.Rows.count
Set rngTarget = sheetRD.Cells(15, 3).End(xlUp).Offset(15, 0) 'Set target range to next available row in Sheet2
For intRow = 1 To rngCopy.Rows.count
Set rngTemp = rngCopy.Cells(intRow) 'Find how many times to copy the row
For I = 1 To noOfIterations
rngTarget.value = rngTemp.value
rngTarget.Borders.LineStyle = xlContinuous
rngTarget.Next.value = rngTemp.Next.value
rngTarget.Next.Borders.LineStyle = xlContinuous
rngTarget.Next.Next.value = rngTemp.Next.Next.value
rngTarget.Next.Next.Borders.LineStyle = xlContinuous
rngTarget.Next.Next.Next.value = rngTemp.Next.Next.Next.value
rngTarget.Next.Next.Next.Borders.LineStyle = xlContinuous
rngTarget.Next.Next.Next.Next.value = "'" & rngTemp.Next.Next.Next.Next.value
rngTarget.Next.Next.Next.Next.Borders.LineStyle = xlContinuous

Set rngTarget = rngTarget.Offset(1, 0) 'Move target range to next row
Next
Next

I could able to retrieve only visible records which are continuous in range.
For example, if user keeps filter any column and if the result set of records are in continuous fashion say rows of ranging from 10 to 20 are visible thenmy code is working good

But when there is no fashion say row 10, row 30 and row 50 are only visible, then my code is not working. its only taking the first record and looping over it.

Could anyone help how to select all the visble range of rows independent of row number?

I could provide you further details


Thanks in advance!
Rajesh

 

[AlphaFrog]

This would copy the visible filtered cells from 5 columns to one contiguous range on a 2nd sheet

    sheetSS.Range("A6", sheetSS.Range("A" & Rows.Count).End(xlUp)).Resize(, 5).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=sheetRD.Cells(15, 3).End(xlUp).Offset(15, 0)

I'm not sure about what destination you want?