how to find used region with macro

  • Thread starter Thread starter Oerjan
  • Start date Start date
O

Oerjan

Hi,

sorry if this obvious or has been asked recently.

I have a macro that goes through all rows and all columns in a
selection. If a row or a column is hidden, the font of that row or
column is changed.

A friend wants to use the macro in a Workbook with many sheets. He
asked if I could change it so that he does not need to do a selection
on every sheet before he runs it.

I imagine that a nested loop through all rows and all columns will
take a long time?

I´ve tried to figure out how to get the range defined by the last row
and the last column filled with data.

First I tried with CurrentRegion but then I tried End(xlUP) but the
code I´ve come up with is not reliable. I get strange results.

I would be most thankful for any hint.

BR,
Oerjan Skogloesa
=============

Sub Makro1()
Dim Sh As Worksheet
Dim myColumn As Range
Dim myRange As Range
Dim A, B, C, D, HiRow, HiCol

For Each Sh In ActiveWorkbook.Worksheets

A = Sh.Rows.Count
HiRow = 1

For Each myColumn In Sh.Columns

B = myColumn.Column
C = (Cells(A, B).Address())

D = Range(C).End(xlUp).Row

If D > HiRow Then HiRow = D

If D > 1 Then HiCol = B

Next myColumn

'this is just for seeing the result
With Sh
..Activate
..Range(.Cells(1, 1), .Cells(HiRow, HiCol)).Select
End With

MsgBox (HiRow & " " & HiCol)

Next Sh

End Sub
 
Your quote "I tried with CurrentRegion". I feel sure that it should work.
Perhaps you were not using it correctly.

Try the following:-

Range("C8").CurrentRegion.Select

You can use any cell in lieu of Cell C8 and it can be any cell within the
range that is not bound by blank cells and it will select all the range
around it until it comes to blank rows and columns surrounding the data.
 
Back
Top