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

harinsh

Active Member
Joined
Feb 7, 2012
Messages
273
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,
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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...
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
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
 
Upvote 0
..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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top