how to select multiple columns with visible cells only - simple code

[ikhan123]

Hi,

i have a table and i want to filter then select multiple columns with visible cells only coz it take to much time to paste.

i want speed and simple coding to do this and i have large database (~1000000) but filter data is only (~300).

below example table started at A2 (A1 = title):

1	a	z	Temp
1	a	z	Temp
1	a	z	Temp
1	a	z	Temp
2	c	za	Data
2	c	za	Data
2	c	za	Data
3	f	xd	Data1
3	f	xd1	Data1
1	a1	z12	Data74
2	CD	vba	code1
1	s1	xa	Data1
3	dd	12	Data1
4	45	45	Data145

<tbody>
</tbody>

Sub Macro2()

    Range("A1").select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$500").AutoFilter Field:=1, Criteria1:="1"
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
'''repeated
    Range("B1").Select
    ActiveSheet.Previous.Select
    Range("D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Range("C1").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1").Select


End Sub

FYI: if i filter data and then select only visible rows (may be 5 or 8) with columns (A & D) it should only take visible rows.


hope i am clear with my question.


Thanks,
ikhan123

 

[ikhan123]

Hi Forum,

Any updates on this?

Regards,
ikhan123

 

[mfexcel]

instead of vba code, did you try the built-in function in Excel? which is Go to Special, visible cell only

Selection.SpecialCells(xlCellTypeVisible).Select
this is the code from Macro recorder. just in case you need it.

 

[ikhan123]

instead of vba code, did you try the built-in function in Excel? which is Go to Special, visible cell only

Selection.SpecialCells(xlCellTypeVisible).Select
this is the code from Macro recorder. just in case you need it.

Sorry. I am new in excel and am not aware of function in excel.

it would be great if you'll provide me the code.

Thank you in advance

 

[mfexcel]

Select the range of cell
Press Ctrl G
Click Special
Select Visible Cells only
OK