Hi there,

I am pretty new to vba and having extreme difficulty programming a loop
that uses SpecialCells(xlvisible)

the macro runs fine if there is something in the filter, the problem is
the data is dynamic and the filter can sometimes produce an empty range
and then the marco gives a run time error


Basically what I want to do is filter a list in place, copy the list
from the second row to the end of the data range for the selected
criteria (leaving off the header) and pasting this in another cell in
another sheet. But if this copied range is empty to skip the copy/paste
altogther.

I would appreciate any help here

I'm not sure how much help I'll be as I don't have your code to look at, but
I'll give it a shot.
Range("YourRange").AutoFilter(...)
If Range("A" & Rows.Count).End(xlUp).Row = _
Range("YourRange").Cells(1).Row Then
'No Filtered Items Move On
Else
Range("YourRange").SpecialCells(xlCellTypeVisible).Copy
End If
--
Charles Chickering

"A good example is twice the value of good advice."


"Mulberry" wrote:

> Hi there,
>
> I am pretty new to vba and having extreme difficulty programming a loop
> that uses SpecialCells(xlvisible)
>
> the macro runs fine if there is something in the filter, the problem is
> the data is dynamic and the filter can sometimes produce an empty range
> and then the marco gives a run time error
>
>
> Basically what I want to do is filter a list in place, copy the list
> from the second row to the end of the data range for the selected
> criteria (leaving off the header) and pasting this in another cell in
> another sheet. But if this copied range is empty to skip the copy/paste
> altogther.
>
> I would appreciate any help here
>
>

Another one:

with .autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
'only headers
else
'do what you want
end if
end with



Mulberry wrote:
>
> Hi there,
>
> I am pretty new to vba and having extreme difficulty programming a loop
> that uses SpecialCells(xlvisible)
>
> the macro runs fine if there is something in the filter, the problem is
> the data is dynamic and the filter can sometimes produce an empty range
> and then the marco gives a run time error
>
> Basically what I want to do is filter a list in place, copy the list
> from the second row to the end of the data range for the selected
> criteria (leaving off the header) and pasting this in another cell in
> another sheet. But if this copied range is empty to skip the copy/paste
> altogther.
>
> I would appreciate any help here

--

Dave Peterson

Just another idea that doesn't use SpecialCells:

Sub Demo()
Dim NumRecords As Long

With ActiveSheet.AutoFilter.Range
NumRecords = WorksheetFunction.Subtotal(2, .Columns(1))

If NumRecords >= 1 Then
'Offset by 1...No headers.
.Offset(1).Copy Worksheets(2).Range("A1")
End If
End With
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Another one:
>
> with .autofilter.range
> if .columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
> 'only headers
> else
> 'do what you want
> end if
> end with
>
>
>
> Mulberry wrote:
>>
>> Hi there,
>>
>> I am pretty new to vba and having extreme difficulty programming a loop
>> that uses SpecialCells(xlvisible)
>>
>> the macro runs fine if there is something in the filter, the problem is
>> the data is dynamic and the filter can sometimes produce an empty range
>> and then the marco gives a run time error
>>
>> Basically what I want to do is filter a list in place, copy the list
>> from the second row to the end of the data range for the selected
>> criteria (leaving off the header) and pasting this in another cell in
>> another sheet. But if this copied range is empty to skip the copy/paste
>> altogther.
>>
>> I would appreciate any help here
>
> --
>
> Dave Peterson

Please don't filter to show empty cells <vbg>.

Dana DeLouis wrote:
>
> Just another idea that doesn't use SpecialCells:
>
> Sub Demo()
> Dim NumRecords As Long
>
> With ActiveSheet.AutoFilter.Range
> NumRecords = WorksheetFunction.Subtotal(2, .Columns(1))
>
> If NumRecords >= 1 Then
> 'Offset by 1...No headers.
> .Offset(1).Copy Worksheets(2).Range("A1")
> End If
> End With
> End Sub
>
> --
> HTH :>)
> Dana DeLouis
> Windows XP & Office 2003
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Another one:
> >
> > with .autofilter.range
> > if .columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
> > 'only headers
> > else
> > 'do what you want
> > end if
> > end with
> >
> >
> >
> > Mulberry wrote:
> >>
> >> Hi there,
> >>
> >> I am pretty new to vba and having extreme difficulty programming a loop
> >> that uses SpecialCells(xlvisible)
> >>
> >> the macro runs fine if there is something in the filter, the problem is
> >> the data is dynamic and the filter can sometimes produce an empty range
> >> and then the marco gives a run time error
> >>
> >> Basically what I want to do is filter a list in place, copy the list
> >> from the second row to the end of the data range for the selected
> >> criteria (leaving off the header) and pasting this in another cell in
> >> another sheet. But if this copied range is empty to skip the copy/paste
> >> altogther.
> >>
> >> I would appreciate any help here
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

I have a similar situation and use this test. In some available cell place
this formula for a column that is exclusively numbers:
=subtotal(9,F2:F2000)
If no data shows with the autofilter, the formula will return "0".

In your code:

sub test()
For each suchand such in sheets

Code
Autofilter
If Range("Z1") = 0 then
goto continue
end if
Do copying etc.
Continue:
Next

end sub
--
Best wishes,

Jim


"Mulberry" wrote:

> Hi there,
>
> I am pretty new to vba and having extreme difficulty programming a loop
> that uses SpecialCells(xlvisible)
>
> the macro runs fine if there is something in the filter, the problem is
> the data is dynamic and the filter can sometimes produce an empty range
> and then the marco gives a run time error
>
>
> Basically what I want to do is filter a list in place, copy the list
> from the second row to the end of the data range for the selected
> criteria (leaving off the header) and pasting this in another cell in
> another sheet. But if this copied range is empty to skip the copy/paste
> altogther.
>
> I would appreciate any help here
>
>

I meant that if the first column of that autofilter range has nothing but empty
cells, then =subtotal(2,xx) will return 0.

If there are no numbers in that visible range, then =subtotal(2,xx) will also
return 0.

=subtotal(3,xx)
will do a counta--but has the same trouble if all the visible cells are empty.

Dave Peterson wrote:
>
> Please don't filter to show empty cells <vbg>.
>
> Dana DeLouis wrote:
> >
> > Just another idea that doesn't use SpecialCells:
> >
> > Sub Demo()
> > Dim NumRecords As Long
> >
> > With ActiveSheet.AutoFilter.Range
> > NumRecords = WorksheetFunction.Subtotal(2, .Columns(1))
> >
> > If NumRecords >= 1 Then
> > 'Offset by 1...No headers.
> > .Offset(1).Copy Worksheets(2).Range("A1")
> > End If
> > End With
> > End Sub
> >
> > --
> > HTH :>)
> > Dana DeLouis
> > Windows XP & Office 2003
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Another one:
> > >
> > > with .autofilter.range
> > > if .columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
> > > 'only headers
> > > else
> > > 'do what you want
> > > end if
> > > end with
> > >
> > >
> > >
> > > Mulberry wrote:
> > >>
> > >> Hi there,
> > >>
> > >> I am pretty new to vba and having extreme difficulty programming a loop
> > >> that uses SpecialCells(xlvisible)
> > >>
> > >> the macro runs fine if there is something in the filter, the problem is
> > >> the data is dynamic and the filter can sometimes produce an empty range
> > >> and then the marco gives a run time error
> > >>
> > >> Basically what I want to do is filter a list in place, copy the list
> > >> from the second row to the end of the data range for the selected
> > >> criteria (leaving off the header) and pasting this in another cell in
> > >> another sheet. But if this copied range is empty to skip the copy/paste
> > >> altogther.
> > >>
> > >> I would appreciate any help here
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson

But if all the visible cells in that range are text, then won't you get 0, too?

And if you're really unlucky, the numbers could sum to 0 (-1+1-3+2+1...)



Jim Jackson wrote:
>
> I have a similar situation and use this test. In some available cell place
> this formula for a column that is exclusively numbers:
> =subtotal(9,F2:F2000)
> If no data shows with the autofilter, the formula will return "0".
>
> In your code:
>
> sub test()
> For each suchand such in sheets
>
> Code
> Autofilter
> If Range("Z1") = 0 then
> goto continue
> end if
> Do copying etc.
> Continue:
> Next
>
> end sub
> --
> Best wishes,
>
> Jim
>
> "Mulberry" wrote:
>
> > Hi there,
> >
> > I am pretty new to vba and having extreme difficulty programming a loop
> > that uses SpecialCells(xlvisible)
> >
> > the macro runs fine if there is something in the filter, the problem is
> > the data is dynamic and the filter can sometimes produce an empty range
> > and then the marco gives a run time error
> >
> >
> > Basically what I want to do is filter a list in place, copy the list
> > from the second row to the end of the data range for the selected
> > criteria (leaving off the header) and pasting this in another cell in
> > another sheet. But if this copied range is empty to skip the copy/paste
> > altogther.
> >
> > I would appreciate any help here
> >
> >

--

Dave Peterson

This is a possibility I had not thought about. In my case the numbers are
always positive and the data in that particular column is always numerical.
Now you.ve gone and got technical on me.
--
Best wishes,

Jim


"Dave Peterson" wrote:

> But if all the visible cells in that range are text, then won't you get 0, too?
>
> And if you're really unlucky, the numbers could sum to 0 (-1+1-3+2+1...)
>
>
>
> Jim Jackson wrote:
> >
> > I have a similar situation and use this test. In some available cell place
> > this formula for a column that is exclusively numbers:
> > =subtotal(9,F2:F2000)
> > If no data shows with the autofilter, the formula will return "0".
> >
> > In your code:
> >
> > sub test()
> > For each suchand such in sheets
> >
> > Code
> > Autofilter
> > If Range("Z1") = 0 then
> > goto continue
> > end if
> > Do copying etc.
> > Continue:
> > Next
> >
> > end sub
> > --
> > Best wishes,
> >
> > Jim
> >
> > "Mulberry" wrote:
> >
> > > Hi there,
> > >
> > > I am pretty new to vba and having extreme difficulty programming a loop
> > > that uses SpecialCells(xlvisible)
> > >
> > > the macro runs fine if there is something in the filter, the problem is
> > > the data is dynamic and the filter can sometimes produce an empty range
> > > and then the marco gives a run time error
> > >
> > >
> > > Basically what I want to do is filter a list in place, copy the list
> > > from the second row to the end of the data range for the selected
> > > criteria (leaving off the header) and pasting this in another cell in
> > > another sheet. But if this copied range is empty to skip the copy/paste
> > > altogther.
> > >
> > > I would appreciate any help here
> > >
> > >
>
> --
>
> Dave Peterson
>