I have a question i cannot solve.

I have some columns set up with filters.

I just added a line into my code to show all the data before the res
of the code was read because i found that if the data was filtered
only the selected data from the filter(the activesheet) was edited wit
the program.

My solution was to add this line :
Worksheets("sheet1").ShowAllData

I have found that when somebody has NOT selected a filter befor
running this macro that i have written, they now get an error . Instea
of using the line that i wrote, Is there a way to make the program loo
at all the data, even the filtered out data if the filter is in use?
here is my little subprogram:

Sub commandbutton2_Click()
Dim answer As Variant


Worksheets(1).ShowAllData
Worksheets(1).Cells.Font.ColorIndex = xlColorIndexAutomatic
Worksheets(1).Interior.ColorIndex = xlColorIndexNone
Worksheets(1).Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
'these previous lines get rid of all the cell shading etc. th
reason being is because some of the called subroutines will ignor
cells with shading or bolding.

Call calc_mean
Call grubbs
Call recalc_meanstdev
Call confidence_interval
Call expected_limits
Call decimalplace



End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

I solved the same problem as follows:

On Error Resume Next
Worksheets("sheet1").ShowAllData
On Error GoTo 0

-----Original Message-----

I have a question i cannot solve.

I have some columns set up with filters.

I just added a line into my code to show all the data
before the rest
of the code was read because i found that if the data was
filtered,
only the selected data from the filter(the activesheet)
was edited with
the program.

My solution was to add this line :
Worksheets("sheet1").ShowAllData

I have found that when somebody has NOT selected a filter
before
running this macro that i have written, they now get an
error . Instead
of using the line that i wrote, Is there a way to make
the program look
at all the data, even the filtered out data if the filter
is in use?
here is my little subprogram:

Sub commandbutton2_Click()
Dim answer As Variant


Worksheets(1).ShowAllData
Worksheets(1).Cells.Font.ColorIndex =
xlColorIndexAutomatic
Worksheets(1).Interior.ColorIndex = xlColorIndexNone
Worksheets(1).Range(Cells(3, "D"), Cells
(700, "P")).Font.Bold = False
'these previous lines get rid of all the cell shading
etc. the
reason being is because some of the called subroutines
will ignore
cells with shading or bolding.

Call calc_mean
Call grubbs
Call recalc_meanstdev
Call confidence_interval
Call expected_limits
Call decimalplace



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Sub showall()
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.ShowAllData
End Sub

try this
with Worksheets(1)
if .autofiltermode=true then .ShowAllData
..Cells.Font.ColorIndex = xlColorIndexAutomatic
..Interior.ColorIndex = xlColorIndexNone
..Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
end with
--
Don Guillett
SalesAid Software

"chick-racer" wrote in message
...

I have a question i cannot solve.

I have some columns set up with filters.

I just added a line into my code to show all the data before the rest
of the code was read because i found that if the data was filtered,
only the selected data from the filter(the activesheet) was edited with
the program.

My solution was to add this line :
Worksheets("sheet1").ShowAllData

I have found that when somebody has NOT selected a filter before
running this macro that i have written, they now get an error . Instead
of using the line that i wrote, Is there a way to make the program look
at all the data, even the filtered out data if the filter is in use?
here is my little subprogram:

Sub commandbutton2_Click()
Dim answer As Variant


Worksheets(1).ShowAllData
Worksheets(1).Cells.Font.ColorIndex = xlColorIndexAutomatic
Worksheets(1).Interior.ColorIndex = xlColorIndexNone
Worksheets(1).Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
'these previous lines get rid of all the cell shading etc. the
reason being is because some of the called subroutines will ignore
cells with shading or bolding.

Call calc_mean
Call grubbs
Call recalc_meanstdev
Call confidence_interval
Call expected_limits
Call decimalplace



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

If Worksheets("Sheet1").FilterMode then
Worksheets("Sheet1").ShowAllData
End If

--
Regards,
Tom Ogilvy

"chick-racer" wrote in message
...

I have a question i cannot solve.

I have some columns set up with filters.

I just added a line into my code to show all the data before the rest
of the code was read because i found that if the data was filtered,
only the selected data from the filter(the activesheet) was edited with
the program.

My solution was to add this line :
Worksheets("sheet1").ShowAllData

I have found that when somebody has NOT selected a filter before
running this macro that i have written, they now get an error . Instead
of using the line that i wrote, Is there a way to make the program look
at all the data, even the filtered out data if the filter is in use?
here is my little subprogram:

Sub commandbutton2_Click()
Dim answer As Variant


Worksheets(1).ShowAllData
Worksheets(1).Cells.Font.ColorIndex = xlColorIndexAutomatic
Worksheets(1).Interior.ColorIndex = xlColorIndexNone
Worksheets(1).Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
'these previous lines get rid of all the cell shading etc. the
reason being is because some of the called subroutines will ignore
cells with shading or bolding.

Call calc_mean
Call grubbs
Call recalc_meanstdev
Call confidence_interval
Call expected_limits
Call decimalplace



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

i've done more testing, i did find that if i replaced the
worksheets('sheet1").showalldata with

selection.AutoFilter Field:=3
I no longer get an error if the filter is being used or not.

I do have another question though. the items that are being filtered
are being re-numbered all the time, so, i'm just wondering if there is
a way to set the filtering back for the user since i have undid the
filter to do the math calculations.?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

thanks so much for all the help.

I find that the "Help" in the excel itself is very poor, actually
find it sucks

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Don,
This is checking the wrong thing. You can have an Autofilter applied
(dropdowns visible), so AutofilterMode would be true, but if there is no
filtering actually being performed (no hidden rows), then ShowAllData will
error. FilterMode will indicate if any rows are hidden by a filter (either
advanced filter or autofilter).

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
Sub showall()
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.ShowAllData
End Sub

try this
with Worksheets(1)
if .autofiltermode=true then .ShowAllData
.Cells.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone
.Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
end with
--
Don Guillett
SalesAid Software

"chick-racer" wrote in message
...

I have a question i cannot solve.

I have some columns set up with filters.

I just added a line into my code to show all the data before the rest
of the code was read because i found that if the data was filtered,
only the selected data from the filter(the activesheet) was edited with
the program.

My solution was to add this line :
Worksheets("sheet1").ShowAllData

I have found that when somebody has NOT selected a filter before
running this macro that i have written, they now get an error . Instead
of using the line that i wrote, Is there a way to make the program look
at all the data, even the filtered out data if the filter is in use?
here is my little subprogram:

Sub commandbutton2_Click()
Dim answer As Variant


Worksheets(1).ShowAllData
Worksheets(1).Cells.Font.ColorIndex = xlColorIndexAutomatic
Worksheets(1).Interior.ColorIndex = xlColorIndexNone
Worksheets(1).Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
'these previous lines get rid of all the cell shading etc. the
reason being is because some of the called subroutines will ignore
cells with shading or bolding.

Call calc_mean
Call grubbs
Call recalc_meanstdev
Call confidence_interval
Call expected_limits
Call decimalplace



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Tom, Of course you are correct. So, why did I change from this
Sub showall()
On Error GoTo away
ActiveSheet.ShowAllData
away:
End Sub

to that?? I have switched back.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
Don,
This is checking the wrong thing. You can have an Autofilter applied
(dropdowns visible), so AutofilterMode would be true, but if there is no
filtering actually being performed (no hidden rows), then ShowAllData will
error. FilterMode will indicate if any rows are hidden by a filter
(either
advanced filter or autofilter).

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
Sub showall()
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.ShowAllData
End Sub

try this
with Worksheets(1)
if .autofiltermode=true then .ShowAllData
.Cells.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone
.Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
end with
--
Don Guillett
SalesAid Software

"chick-racer" wrote in message
...

I have a question i cannot solve.

I have some columns set up with filters.

I just added a line into my code to show all the data before the rest
of the code was read because i found that if the data was filtered,
only the selected data from the filter(the activesheet) was edited
with
the program.

My solution was to add this line :
Worksheets("sheet1").ShowAllData

I have found that when somebody has NOT selected a filter before
running this macro that i have written, they now get an error .
Instead
of using the line that i wrote, Is there a way to make the program
look
at all the data, even the filtered out data if the filter is in use?
here is my little subprogram:

Sub commandbutton2_Click()
Dim answer As Variant


Worksheets(1).ShowAllData
Worksheets(1).Cells.Font.ColorIndex = xlColorIndexAutomatic
Worksheets(1).Interior.ColorIndex = xlColorIndexNone
Worksheets(1).Range(Cells(3, "D"), Cells(700, "P")).Font.Bold = False
'these previous lines get rid of all the cell shading etc. the
reason being is because some of the called subroutines will ignore
cells with shading or bolding.

Call calc_mean
Call grubbs
Call recalc_meanstdev
Call confidence_interval
Call expected_limits
Call decimalplace



End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements