Excel VBA :How to choose only active area in worksheet with non-specific Cell reference

Question

I am building a code to re-format (underlining, coloring)parts of any active worksheet (not a specific one). The code works but it is limited in that I am setting cells arbitrarily.

That is, for the first formatting function below what I am using m=2 To 500 n = 2 To 200 and I am testing the macro in a worksheet which contains values in A1:L150. What I really need is to define dynamically the active area (except for first row and first column) i.e the worksheet area that has values. So for example if a worksheet has values in the range A2:M1055 I would need the formatting to happen in this area. If yet another worksheet has cells populated in the area A2:O1500 formatting would cover that latter area. So the user would not have to define the area ideally (unless there is no other way). Same for the second formatting: I would need the entire column.

Is there a way to achieve this using Cells() ideally?
If not what would be an alternative?

The worksheet is not a table and has many blank cells.

Option Explicit
Dim ws As Worksheet
Dim m As Long
Dim n As Long

Sub format
  
Set ws = ActiveWorkbook.ActiveSheet
ws.Activate

'formatting sheet except first row  and first column 
For m = 2 To 500 
For n = 2 To 200

        If Cells(m, n).HasFormula = False Then
        Cells(m, n).Interior.Color = RGB(0, 255, 0)
        ElseIf Cells(m, n)...        
        End If
    Next n
Next m

Set ws = ActiveSheet
ws.Activate

'formatting first column
For m = 2 To 100
    For n = 1 To 1

        If Cells(m, n).HasFormula = False Then
        Cells(m, n).Interior.Color = RGB(255, 0, 0)

It is not quite clear what you mean by "active area". Do you mean the current [`Selection`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.selection)? Or the [`UsedRange`](https://learn.microsoft.com/en-us/office/vba/api/Excel.Worksheet.UsedRange) of the [`ActiveSheet`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.activesheet)? Or, something else entirely? — Chronocidal, Sep 01 '20 at 23:00
https://stackoverflow.com/questions/42078473/last-used-cell-in-sheet — Tim Williams, Sep 01 '20 at 23:26

score 0 · Answer 1 · edited Sep 02 '20 at 07:07

Using your existing code you can use the range selection if you have a known starting point and excel will select to the last record and last column of data.

The Selection.Address can give you the values such as A1:L150. I used split to separate the A1 and L150, you can use mid to pull out the letter and number but be careful if you go beyond the z column. You can change the reference to ReferenceStyle:=xlR1C1 to get the Value as R1C1:R150C12. Play around with RowAbsolute and ColumnAbsolute to get what you want.

Dim ws As Worksheet
Dim m As Long
Dim n As Long
Dim sRange As String
Dim sRanges() As String

Set ws = ActiveWorkbook.ActiveSheet
ws.Activate

    ws.Range("A2").Select
    ws.Range(Selection, Selection.End(xlDown)).Select
    ws.Range(Selection, Selection.End(xlToRight)).Select

    sRange = Selection.Address(ReferenceStyle:=xlA1, _
                           RowAbsolute:=False, ColumnAbsolute:=False)
    sRanges = Split(sRange, ":")
    Debug.Print sRanges(0)
    Debug.Print sRanges(1)

'Prints A1 and L150

score 0 · Answer 2 · answered Sep 02 '20 at 11:59

Many thanks for the replies received. I have tried all solutions with different worksheets. The one that worked with all worksheets is the one entitled "Use Find both by row and column to identify this cell" found in the link suggested by @Tim Williams

I think its because of the somewhat complex formatting of the worksheets and blank cells (or my poor skills) that I can't get the solution by @Reino4 to work; for worksheets without blank cells this works fine too: thank you! Much appreciated!

I am not entirely sure how I vote the response by Tim.

Also , thanks for the editing of the post

Stephan

Excel VBA :How to choose only active area in worksheet with non-specific Cell reference

2 Answers2