CODEWITHSUNDEEP
excelvba
Stacey
Stacey

Reputation: 103

Change the column width and row height

I have a workbook with 100-150 worksheets I have created a code which will change the row height and column width of all the worksheets except the worksheets with the following name : "Cover" "Trans_Letter", "Abbreviation" and sheet name ending with _Index.

My code is working fine and changing row height and column width, however it is also changing column width and row height for the sheet names ending with _Index.

Please suggest how should I modify the code so that while looping it should skip the sheet names ending with _Index.

I guess the following line needs to be modified:

If ShtNames(Z) <> "Trans_Letter" And ShtNames(Z) <> "Cover" And ShtNames(Z) <> "Abbreviations" And InStr("_Index", ShtNames(Z)) = 0 Then

Please find below the code:-

Sub rowcolallsheetbtransletter()

    Dim exworkb As Workbook
    Dim xlwksht As Worksheet
    Dim lastrow1 As Long
    Dim lastcolumn1 As Long
    Dim firstrowDB As Long
    Dim Z As Integer
    Dim ShtNames() As String

    ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)

    For Z = 1 To Sheets.Count

        ShtNames(Z) = Sheets(Z).Name

        If ShtNames(Z) <> "Trans_Letter" And ShtNames(Z) <> "Cover" And ShtNames(Z) <> "Abbreviations" And InStr("_Index", ShtNames(Z)) = 0 Then

            Sheets(Z).Select

            lastrow1 = Sheets(Z).Cells(Rows.Count, "A").End(xlUp).Row
            lastcolumn1 = Sheets(Z).Cells(1, Columns.Count).End(xlToLeft).Column

            ActiveWorkbook.Sheets(Z).Range(Sheets(Z).Cells(1, 2), Sheets(Z).Cells(lastrow1, lastcolumn1)).Select

            Selection.Cells.RowHeight = 67
            Selection.Cells.ColumnWidth = 30

        End If
    Next Z
End Sub

Upvotes: 2

Views: 458

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Instead of checking whether the sheet name exists within the string "_Index", check to see if "_Index" exists within the sheet name:

If ShtNames(Z) <> "Trans_Letter" And _
   ShtNames(Z) <> "Cover" And _
   ShtNames(Z) <> "Abbreviations" And _
   InStr(ShtNames(Z), "_Index") = 0 Then

Upvotes: 1

Related Questions