Column width and Row height test?

[CLR]

Hi All...
Is it possible to test a range of cells, for column widths and row heights,
(or maybe anything outside the default settings)?.......also to tell if any
rows or columns within that range are hidden or merged? Any info that can
be decerned would be appreciated.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[AltaEgo]

Below are a set of samples that should give you a starting point. There are
many variations so, try to work out what you are trying to achieve from the
sample below and ask further questions if you are stuck.
Generally speaking, what you can do with a row, you can do with a column.
Use EntireColumn instead of EntireRow.




Sub Test()

If Selection.ColumnWidth = 10 Then
MsgBox "Column Width exceeds 10."
End If
If Selection.RowHeight > 10 Then
MsgBox "Row Height exceeds 10."
End If

'if A2 row is hidden
'set a row height according to contents
If Range("A2").EntireRow.Hidden = True Then
Range("A2").EntireRow.AutoFit
' see next sub
End If
'Is A2 merged
MsgBox Range("A2").MergeCells

End Sub

Sub HideRowIfBlank()

If Range("A2").Value & "" = "" Then
'hide the row
Range("A2").EntireRow.RowHeight = 0
Else
'unhide and size it to 25
Range("A2").EntireRow.RowHeight = 25
End If

End Sub

Sub SetA2Val()
'use to set a value in A2
'after the row is hidden
Range("A2") = 1
End Sub

As with many problems in Excel, there are other solutions.

Range("2:2").RowHeight = 12

Cells(2,1).entireRow.Autofit


Sub JustForFun()

For i = 1 To 20
Cells(i, 1).EntireRow.RowHeight = i
Cells(1, i).EntireColumn.ColumnWidth = i / 6
Next i

 

[CLR]

Many thanks, Steve.........
Your suggestions are a lot of help....I appreciate them.
What I'm doing is.....I've just received a new file download from an ORACLE
system in .xls, but formatted to what a ORACLE person thinks is good,
including many variable size, or hidded, columns and rows. My taks is to
massage that data into a format/program that meets our needs. I've done
this before with straight .xls files downloaded from a different computer
system, so know the ropes. I can do this one too, but must be sure they
send me a file formatted the SAME way each month, in order for my automatic
macros to work more easily. So I want to "profile" this one, and then be
able to run that "profiler" against the new file next month to see if they
have changed anything besides the data.

End goal I guess is to have a macro that will insert a new Row1 and ColumnA
and step through and insert the RowHeights and ColumnWidths for the entire
used area.

Thanks again for your help,
Vaya con Dios,
Chuck, CABGx3

 

[Rick Rothstein]

If it helps you any, you can set all the column widths and row heights in a
single statement for each (this will also unhide any hidden rows and/or
columns)...

ActiveSheet.Cells.ColumnWidth = 8.43
ActiveSheet.Cells.RowHeight = 12.75

Of course, you can use Worksheets("Sheet1") in place of ActiveSheet to set
these on a specific, non-active sheet if necessary.

 

[CLR]

Thanks for that info Rick........don't need it right now, but will keep it
for my stash for sure........appreciate your response.

Vaya con Dios,
Chuck, CABGx3