Can't see the wood for the trees

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
I have something that's driving me bonkers

Code:
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

Code:
.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

Legend
Joined
Jun 19, 2002
Messages
63,651
Either of those statements could fail if there were no visible cells in the range. Try

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
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

Moderator
Joined
Oct 23, 2004
Messages
9,899
My soloution which seems to work
Code:
'*****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
 

Forum statistics

Threads
1,082,283
Messages
5,364,268
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top