I want all tables on all sheets to be formatted in a certain way.

%3CLINGO-SUB%20id%3D%22lingo-sub-2556360%22%20slang%3D%22en-US%22%3EI%20want%20all%20tables%20on%20all%20sheets%20to%20be%20formatted%20in%20a%20certain%20way.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556360%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%3C%2FP%3E%3CP%3EI%20want%20all%20tables%20on%20all%20sheets%20to%20be%20formatted%20in%20a%20certain%20way.%3CBR%20%2F%3EIt%20turns%20out%20that%20an%20error%20always%20appears%20on%20the%20%22.AutoFilter.ShowAllData%22%20line.%3CBR%20%2F%3EThe%20error%20is%20as%20follows%3A%20%22The%20object%20variable%20or%20the%20variable%20in%20the%20width%20block%20has%20not%20been%20defined%22%3C%2FP%3E%3CP%3ETwo%20questions%3A%3CBR%20%2F%3E1st%20I%20thought%20it%20was%20because%20one%20of%20the%20tables%20was%20empty%2C%20but%20they%20all%20have%20data.%20Why%20the%20error%3F%3C%2FP%3E%3CP%3E2nd%20If%20one%20of%20the%20tables%20is%20empty%2C%20is%20there%20an%20error%3F%20If%20so%2C%20how%20can%20I%20anticipate%20this%20problem.%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%3C%2FP%3E%3CP%3E-------------------------------------------------%20-------------------------%3C%2FP%3E%3CP%3ESub%20Format_AllTables1()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Dim%20T%20As%20ListObject%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Dim%20i%20As%20Long%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20For%20i%20%3D%201%20To%20Sheets.Count%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20For%20Each%20T%20In%20Sheets(i).ListObjects%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20With%20T%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.Sort.SortFields.Clear%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.AutoFilter.ShowAllData%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.TableStyle%20%3D%20%22%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20With%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20With%20T.HeaderRowRange%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.Interior.Color%20%3D%20RGB(49%2C%20133%2C%20156)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.Font.Size%20%3D%2010%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20With%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Next%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Next%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2556360%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556468%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20all%20tables%20on%20all%20sheets%20to%20be%20formatted%20in%20a%20certain%20way.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084899%22%20target%3D%22_blank%22%3E%40csoares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20routine%20worked%20fine%20for%20me.%26nbsp%3B%20I%20would%20prefer%20white%20text%20on%20the%20dark%20teal%20header%20bar%20and%20I%20like%20a%20bottom%20border%20on%20the%20table%20but%20such%20things%20are%20irrelevancies.%26nbsp%3B%20My%20table%20only%20had%20data%20on%20one%20of%20the%20rows%20and%20then%20only%20in%20two%20columns.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20295px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296420i65F1353ED8553941%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good afternoon

I want all tables on all sheets to be formatted in a certain way.
It turns out that an error always appears on the ".AutoFilter.ShowAllData" line.
The error is as follows: "The object variable or the variable in the width block has not been defined"

Two questions:
1st I thought it was because one of the tables was empty, but they all have data. Why the error?

2nd If one of the tables is empty, is there an error? If so, how can I anticipate this problem.

Thank you for your help

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

Sub Format_AllTables1()
          Dim T As ListObject
          Dim i As Long
          For i = 1 To Sheets.Count
                  For Each T In Sheets(i).ListObjects
                          With T
                                  .Sort.SortFields.Clear
                                  .AutoFilter.ShowAllData
                                  .TableStyle = ""
                          End With
                          With T.HeaderRowRange
                                  .Interior.Color = RGB(49, 133, 156)
                                  .Font.Size = 10
                          End With
                  Next
          Next
End Sub

1 Reply

@csoares 

Your routine worked fine for me.  I would prefer white text on the dark teal header bar and I like a bottom border on the table but such things are irrelevancies.  My table only had data on one of the rows and then only in two columns.

image.png