Copy data with filter applied using Excel VBA

0 votes

Using Range, I discovered that. Range and Copy. SpecialCells(xlCellTypeVisible). I'm not sure why copy produces the same outcome; what is the real distinction between the two?

Using the following spreadsheet as a simple illustration (from A1:C6)

 B C
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5

If I filter out 3, then the visible data becomes:

A B C
1 1 1
2 2 2
4 4 4
5 5 5

When I was trying to copy the above data with the filter applied to another range, I tested the following two methods had the same results, which only copied the data with "3" filtered out, but why?

Mar 19, 2023 in Others by narikkadan
• 63,600 points
535 views

1 answer to this question.

0 votes

Try this:

Private Sub CommandButton1_Click()
   Dim v As Variant, r As Range, dest As Range
   Set r = Me.Range("A1:C6")  'filtered range to copy
   v = r
   Set dest = Me.Range("D10").Resize(r.Rows.CountLarge, r.Columns.CountLarge)
   dest = v
End Sub

the result of pressing the button

answered Mar 19, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,082 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
908 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
1,392 views
0 votes
1 answer

Paste special issue, From Excel to Word

Since the table in the word document ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 63,600 points
608 views
0 votes
1 answer

Copying and pasting from one workbook to another doesn't work

Your ranges aren't fully qualified. Excel will make ...READ MORE

answered Feb 18, 2023 in Others by narikkadan
• 63,600 points
465 views
0 votes
1 answer

Filter outlook sent items in vba failing for emails with multiple recipients

The PR DISPLAY TO parameter provides a ...READ MORE

answered Feb 21, 2023 in Others by narikkadan
• 63,600 points
786 views
0 votes
1 answer

Export multiple worksheets without formula with the ability to select exact sheets and location

Try this: Sub ExportSheets() Dim ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,000 points
552 views
0 votes
1 answer

Excel: importing data from another Excel file using VBA

Refer to the file variables folderName and ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,000 points
676 views
0 votes
1 answer

How do ask ChatGPT with API from Excel macros (vba)?

A few things that will help. Don't ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,000 points
2,182 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP