Hi All,
Could anyone guide me to detect when, after an autofilter search (usin
a UserForm), the criteria1 results in "no cells".
Let me try to explain me better. I have two sheets, in sheet1 I have
"ListBox1" wich updates after the sheet2, wich contains a table, i
modified with Autofilter operations made with a UserForm that run
from Sheet1 with a click button.
My problems araise when the criteria used doesn't generate visibl
cells, at the moment of updating sheet1, generates an error that say
"No cells were found".
This is the code (Provided by Dave and others in this forum) in sheet
to update the ListBox1
Code
-------------------
Private Sub Worksheet_Activate()
Dim wks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim myCell As Range
Dim iCtr As Long
Sheets("Historia").ScrollArea = "A1:N45"
'Worksheets("BDHistoria").Visible = True
'Worksheets("BDHistoria").Select
Set wks = Worksheets("BDHistoria")
If Sheets("BDHistoria").EnableAutoFilter = True Then
'Deja como esta
Else:
Worksheets("BDHistoria").Range("A3").AutoFilter VisibleDropDown:=True
End If
Set rng = wks.AutoFilter.Range
With rng
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
End With
With Sheets("Historia").ListBox1
.ListFillRange = ""
.Clear
.ColumnCount = rng.Columns.Count
For Each myCell In rngF.Cells
.AddItem (myCell.Text)
For iCtr = 1 To rng.Columns.Count - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Text
Next iCtr
Next myCell
End With
1:
Sheets("Historia").Select
End Su
-------------------
The line where the macro breaks in error is this:
Code
-------------------
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0).Cells.SpecialCell
-------------------
Hope the explanation was a bit more clear :).
Thanks in advance for your guidance.
Saludos
Jose Lui
--
jose lui
-----------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...fo&userid=1331
View this thread:
http://www.excelforum.com/showthread.php?threadid=38340