Can't see the wood for the trees

[mole999]

I have something that's driving me bonkers

Sub PrepareData()
Sheets("MasterData").Select
    Rows("1:1").Select
    Selection.AutoFilter
    
    '** Routines to delete large unecessary portions of file, i.e, wrong area

Selection.AutoFilter Field:=1, Criteria1:="CAC"
Range("A1").Select
AA = Range("a15000").End(xlUp).Row
BB = Range("b15000").End(xlUp).Row
CC = Range("g15000").End(xlUp).Row
DD = Range("l15000").End(xlUp).Row
FF = Application.Max(AA, BB, CC, DD)
zy = "A1:K" & FF
Range(zy).Select
With Sheets("MasterData").Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
End With
Selection.AutoFilter Field:=1

DoEvents 'added following reading to allow time
    
Selection.AutoFilter Field:=1, Criteria1:="DEC"
Range("A1").Select
AA = Range("a15000").End(xlUp).Row
BB = Range("b15000").End(xlUp).Row
CC = Range("g15000").End(xlUp).Row
DD = Range("l15000").End(xlUp).Row
FF = Application.Max(AA, BB, CC, DD)
zy = "A1:K" & FF
Range(zy).Select
With Sheets("MasterData").Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
End With
Selection.AutoFilter Field:=1

these two statements exist together, the first runs through no problem at all, the second stalls on the line below, both columns have more than ample values to select from, and I am selecting the whole width of data, and i need to clear the data. The failure is RunTime Error 1004

.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear

It's so identical that the working one was pasted over the non working and still fails. must be really simple, please show me what my eyes cannot see

 

[VoG]

Either of those statements could fail if there were no visible cells in the range. Try

On Error Resume Next
.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
On Error GoTo 0

 

[mole999]

Tried that, 'Error 0' added it in different places as well, even been looking at available memory and threads, nothing excessive occurring, going to try and add an unnecessary sort to move data up page before the next filter, to see if that will allow it to step forward

 

[mole999]

My soloution which seems to work

'*****Full Routine Start*****
Rows("1:1").Select
If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("1:1").AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="DEC"
Range("A1").Select
AA = Range("a15000").End(xlUp).Row
BB = Range("b15000").End(xlUp).Row
CC = Range("g15000").End(xlUp).Row
DD = Range("k15000").End(xlUp).Row
FF = Application.Max(AA, BB, CC, DD)
zy = "A2:K" & FF
Range(zy).Select
With Sheets("MasterData").Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
    On Error GoTo 0
End With
Sort < added to avoid keep recoding
'*****Full Routine Finish*****
[/copy]

Hardly elegant but seems to work, i just keep repeating the mini block