Row Hieght/Coulmn Width

  • Thread starter Thread starter Dorian C. Chalom
  • Start date Start date
D

Dorian C. Chalom

How can I copy the Row Hieght and Column Width from one sheet to another
within the same workbook?
 
In general

Sheets(2).Rows(1).RowHeight = Sheets(1).Rows(1).RowHeight
Sheets(2).Columns(1).ColumnWidth = Sheets(1).Columns(1).ColumnWidth

Or assign the source sheet height and width to variables and use that

e.g for height.....

Dim iH As Double
iH = Sheets(1).Rows(1).RowHeight

Sheets(2).Rows(1).RowHeight = iH

you can also assign this to more than one row (or column).

Sheets(2).Rows("1:10").RowHeight = iH
 
OK I was thinking like your first example...
But lets say I want to loop through all the rows and columns.
How do I determine the last row and column used?
Can I do that?
 
Depending on what you want...

For last displayed value
==========================
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column

For last value or formula
===============================
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column

That "last value or formula" one will find the last cell with an actual
value or with a formula in it even if the formula evaluates to the empty
string ("").
 
Back
Top