VBA Autofilter for Mac to copy rows from one sheet to another

[realdemigod]

Hi,
I wasted a lot of time trying to make a VBA code with Autofilter work in Mac but I understand that Excel Mac does not support Autofilter VBA function. Could you anyone suggest an easier alternative?

I have around 8 lakh rows on Sheet1 and I need to copy rows based on a particular cell value present in column 21 from Sheet1 to Sheet2. So I tried with a code which looks like this.

Sub movedata()

Dim sh As Sheet1
Dim rng As Range

Set rng = sh.Range("A1", sh.Range("A1").End(xlDown)).Select
On Error Resume Next
rng.AutoFilter Field:=21, Criteria1:="UK"
SpecialCells(xlCellTypeVisible).EntireRow.Copy
Sheets(“Sheet2”).Range(“A1”)
.AutoFilter
End With

End Sub

I'm not able to make this work. Pease help, thanks.

 

[starl]

Even on a PC, I wouldn't recommend using AutoFilter with code. If you've got formulas in any columns, it can be VERY slow. I prefer the following trick that I learned from @Zack Barresse. I call it the 'Zack method.' It consists of using an additional column to do the required filter logic (eg U2 <> "UK") and using resulting calculation with SpecialCells to select only the rows you want.

So, assuming your data set (with header) is in A1:U20 (I'm hardcoding rows & columns, but you may want to make it more dynamic):

Sub BetterThanFilter()
Dim rngFormula As Range

With Worksheets("Sheet1")
    Set rngFormula = Range("V2:V20")

    'this will put a 1 if there is a UK, and a #DIV/0! error if not
    rngFormula.FormulaR1C1 = "=1/(RC[-1]=""UK"")"

    'copy the matches or use xlErrors to get the non-matches)
    rngFormula.SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Copy Worksheets("Sheet2").Range("A1")

    'clear formula columns
    rngFormula.Delete (xlShiftToLeft)
    Sheet2.Range("V1").EntireColumn.Delete (xlShiftToLeft)
End With

Set rngFormula = Nothing
End Sub

 

[RoryA]

FWIW, the problem is not a Mac one, it's just that your code is completely wrong. You'd need something like this:

Sub movedata()

Dim sh As Worksheet
Dim rng As Range
Set sh = Sheet1
Set rng = sh.Range("A1").CurrentRegion
On Error Resume Next
rng.AutoFilter Field:=21, Criteria1:="UK"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A1")
rng.AutoFilter
End Sub

 

[starl]

bah. I still prefer my solution @RoryA

 

[realdemigod]

@starl - thanks for your help, I'm not good with VBA as you can see so I will try your code later and get back to you as I'm short on time against my deadline as RoryA's code worked easily

@RoryA - thanks for your help it is working

 

[starl]

@realdemigod - no worries! glad Rory popped in.. I'm just embarrassed by the fact that I didn't read your code more closely and find the issue.

 

[Zack Barresse]

Ha! Yeah, the helper column method shines more in very large data sets as it's so much faster than traditional filtering in those situations. Great tool for the toolbox though.