VBA - Check if named range is hidden. If not hidden, AutoFit cell Row height

Question

I have a dynamic table that is tied to a SharePoint list. At any given time only 1 column will be visible for report generated on the spreadsheet. As the table may grow or shrink, I need a routine that can adjust to any number of rows and then look look over through each row to determine if that row's cell is visible.

If the cell is visible, I need the routine to AutoFit based upon row height.

I've been able to get this to work with a static range, but can't seem to get it working using a named range.

I've built the routine to run two nested loops: one to look down through the rows, and a second nested loop to look right through the columns.

The error I'm getting is: "Run-Time error '438': Object doesn't support this property or method".

The error occurs here:

If Worksheets("owssvr").rowcurrange.EntireColumn.Hidden = False Then

Any help would be very very much appreciated!

Thanks!

Public Sub AutoFit()
'Sub to autofit the contents of the desired field in the report.
'The routine loops through the table to determine the visible cells, and if visible, autofits the contents

Dim lastrow As Long 'lastrow of table
Dim rowNumber As Long 'counter to determine current visible row number of table
Dim columnrange As Range 'full column range of table
Dim cell As Range 'range used to check if row is visible
Dim rowrange As Range 'loops through row range to determine if current cell is hidden, if so autofit
Dim rowcurrange As Range 'current cell of rowrange

rowNumber = 4
lastrow = Worksheets("owssvr").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set columnrange = Worksheets("owssvr").Range("A" & rowNumber & ":A" & lastrow) 'sets range to check autofit

For Each cell In columnrange 'loops through and checks to see if current row is visible

    Set rowrange = Worksheets("owssvr").Range("G" & rowNumber & ":AR" & rowNumber) 'set current row to dnyamically autofit

    For Each rowcurrange In rowrange 'loops through current row to check if row is hidden, if visible autofit rowcurrange
    MsgBox (rowcurrange)
    If Worksheets("owssvr").rowcurrange.EntireColumn.Hidden = False Then

        Worksheets("owssvr").rowcurrange.Rows.AutoFit 'autofits only the field cell
        MsgBox (rowcurrange)

    End If
    Next

    rowNumber = rowNumber + 1
Next

End Sub

Sobigen Sobigen · Accepted Answer · 2015-03-27T18:53:50

Change your broken line to

If rowcurrange.EntireColumn.Hidden = False Then

You're over specifying the range. rowcurrange already has the worksheet information necessary. The error is due to Worksheets("owssvr") not having a method called rowcurrange.

A named range approach would actually look like this. You have a range object.

If Worksheets("owssvr").range("MyNamedRange").EntireColumn.Hidden = False` Then

VBA - Check if named range is hidden. If not hidden, AutoFit cell Row height

1 Answers