vba-showalldata

[chick-racer]

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

 

[Claude]

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


------------------------------------------------

~~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 Guillett]

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
(e-mail address removed)

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


------------------------------------------------



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

creating financial statements

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

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


------------------------------------------------



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

creating financial statements

 

[chick-racer]

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.?

 

[chick-racer]

thanks so much for all the help.

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

 

[Tom Ogilvy]

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).

 

[Don Guillett]

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.