get range and copy part of range (1 Viewer)

smiler44 · Feb 16, 2017

I want to filter a spread sheet. There may be just one row plus the header of more rows plus the header.

I want to copy column A and first visible cell after the header to column C and last used visible cell.

so I filter and there are two entries, row 10 and row 99.
I want to copy A10 to C99 visible cells only. I don't cant anything from column D onwards.

next filter and it is row 33 and 45 I want to copy A33 to C45.

how?......please?

everyone else gone home and I'm still stuck at my desk

thank you

smiler44

smiler44 · Feb 16, 2017

more searches via Google and getting different bits of code I have come up with this. It filters, copies just the cells I want from the visible cells and pastes to another sheet.
I will add a do until loop as I need to go back to sheet1, filter for my next word, copy the data and paste but this is a major milestone for the night.
code may not be pretty, I may not even understand it but at this time of night, it works and that will do for tonight. Tomorrow I may even understand it.

smiler44

Code:

Sub Macro2()
Dim a1141 As String
Dim fcrow As String 'first current row
Dim lcrow As String 'last current row

    Windows("MISSING a1141 CODES.xlsm").Activate
    Sheets("sheet1").Select
    Range("A2").Activate
    a1141 = ActiveCell.Value
    
find1141:
   ActiveSheet.Range("$A$1:$BO$5403").AutoFilter Field:=1, Criteria1:=a1141
Range("a2", Cells(Rows.Count, "a").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Activate
    fcrow = ActiveCell.Row
 
 ActiveSheet.Range("$a$2:$a$7000").AutoFilter Field:=1, Criteria1:=a1141
    ActiveSheet.Range("$a$2:$a$7000").AutoFilter Field:=1, Criteria1:=a1141
Rng = ("A" & crow) & ":" & ("B" & lcrow)
    Sheet1.Range(Rng).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("Sheet2").Range("A1").Offset(ColumnOffset:=1)

get range and copy part of range (1 Viewer)

smiler44

Registered User.

smiler44

Registered User.

Similar threads

Users who are viewing this thread