Copy Visible Cells after filter if no data don't copy

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
Hi Team,

I know this is something can get it online but I could not get it and leads to lots of confusion. I need help in copying visible cells.

For Example I have columns from A to AE and want to filter something on column AE and code should select only visible cells post filtered and copy only those cells excluding column header and in case no data found after filter then code should not copy and end the condition.

Could you please help me with solution.

Thank you,
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
You should tell us exactly what your wanting to do.

Sounds like you have some code your using but it is not working for you and you need help.

We need to know what you want to filter and when you copy the filtered data where do you want to paste it.
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
Hello,

I am looking for how to select visible cells and copy & paste and if no data found then it should not copy the cells. Currently my code copying header if there is no data.

Let me give with example: Assume you have below data in sheet 1 and if want to filter data which contain column SKU i.e XR23423 then I will get couple of rows which contain this value and want to copy those lines into different sheet assume its sheet 2.

SKUDESCBIN #LOCATION
SP7875Item 1T345Row slot 1
TR87680Item 2T345Row slot 1
MK676554Item 3T5789Row
YE98767Item 4T9876Row slot 2
XR23423Item 5T098Row slot 1
PW98762Item 6T345Row, slot 1
BM87684Item 7T349Row 1, slot
BH67655Item 8T5789Row 1, slot
WT98768Item 9T9875Row 2, slot
TS3456Item 10T349Row 1, slot
WDG123Item 11T349Row 1, slot

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


I hope i clarified you...
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
Is there some reason you cannot tell me what column SKU is ?

Can you not say column A or column B

Or does this change all the time?

Or do you want to search column AE
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
And will we always be looking for this same value:
XR23423

If not then we will need a Inputbox where you will enter the search value.
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
I just given you the Example data ...data will change but logic remains same....not require inputbox because its simple filter the data and copy and paste from sheet 1 to sheet 2 ...its about column only not SKU...I just mentioned header name ...I am looking very basic selection post filter and select only visible cells and paste those values in different sheet....
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
Well since you just want a example you can try this and modify to your needs.

Code:
Sub Filter_Me_OK()
'Modified 6/24/18 4:00 PM EDT
Dim Lastrow As Long
Dim Lastrowa As Long
Dim c As Long
Dim s As String
Dim ans As String
ans = InputBox("Enter search value")
c = "31" ' Column Number Modify this to your need
s = ans 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, c).End(xlUp).Row + 1
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(Lastrowa)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
259
..this is exactly what I am looking ....especially below code works perfect ....Thank you very much

counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(Lastrowa)
Else
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
..this is exactly what I am looking ....especially below code works perfect ....Thank you very much

counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(Lastrowa)
Else
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top