How to set Column A Between Two Named Rows as a Range?

Question

Struggling to Set the Range in Column A.

The number of rows for the data is always changing so I've named the upper and lower rows of the data set.

If the cell contains text and not a number, I want to change the font to white.

Sub DetailHide()

    Dim RangeToConsider As Range
    Dim shtSrc As Worksheet
    
    Set shtSrc = Sheets("Est Dtl")
    Set RangeToConsider = shtSrc.Range(.Cells(.Range("EstimateDetailHeader").Row + 1, "A"), _
                                       .Cells(.Range("BorderLastRow").Row - 4, "A"))
    For Each Cell In RangeToConsider
        If IsNumeric(Cell) = False Then
            Cell.Select
            Selection.Font.Color = white
        End If
    Next Cell
End Sub

A compile error

Invalid or unqualified reference

occurs at setting the RangeToConsider .Cells(.Range point.

Remove the dots, why have you added those? Just `Cells ...`. Dots are only used with `With` statement. — SJR, Dec 21 '20 at 16:19
No.Do not remove the dots but put `shtSrc` before every dot in that line if it is already not there. Alternatively use `With shtSrc.. End With` and put the `Set` statement inside it. — Siddharth Rout, Dec 21 '20 at 16:22
[Interesting Read](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) — Siddharth Rout, Dec 21 '20 at 16:23
I'm seriously confused. I tried removing the periods or adding `shtSrc` and it doesn't work. Instead I get an `Expected: end of statement` error for the comma after the first `"A")`. I read the linked question and it confused me even more since it appears different than what I'm trying to accomplish. — Neuner, Dec 21 '20 at 16:40
@SiddharthRout can you explain further? From what I've read, your reference states that it is to `= Value` but mine is Setting a Range so I don't understand. Not able to get the suggestions to work. Thanks! — Neuner, Dec 21 '20 at 17:31
Is this what you tried? `Set RangeToConsider = shtSrc.Range(shtSrc.Cells(shtSrc.Range("EstimateDetailHeader").Row + 1, "A"), _ shtSrc.Cells(shtSrc.Range("BorderLastRow").Row - 4, "A"))` — Siddharth Rout, Dec 21 '20 at 17:40
Also change `Cell.Select Selection.Font.Color = white` to `Cell.Font.Color = white` — Siddharth Rout, Dec 21 '20 at 17:41
@SiddharthRout yes, and it gives me the error; "1004: Method 'Range' of object'_Worksheet' failed" — Neuner, Dec 21 '20 at 19:12
@Neuner, i changed the line in my answer to reflect your sheet-Object (Sheets(1) -> Sheets("Est Dtl")) — JollyRoger, Dec 21 '20 at 19:46
Put a breakpoint on that line and check what values do you get for `shtSrc.Range("EstimateDetailHeader").Row` and `shtSrc.Range("BorderLastRow").Row` — Siddharth Rout, Dec 21 '20 at 20:19

JollyRoger · Accepted Answer · 2020-12-21T19:42:21.260

1

This code should work:


Sub DetailHide()
    Dim RangeToConsider As Range
    Dim shtSrc As Worksheet
    
    Set shtSrc = Sheets("Est Dtl")
    Set RangeToConsider = shtSrc.Range(shtSrc.Cells(Range("EstimateDetailHeader").Row + 1, "A"), _
                                   shtSrc.Cells(Range("BorderLastRow").Row - 4, "A"))
    For Each Cell In RangeToConsider
        If IsNumeric(Cell) = False Then
            Cell.Select
            With Selection.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
        End If
    Next Cell
End Sub

edited Dec 21 '20 at 19:42

answered Dec 21 '20 at 19:19

JollyRoger

329
1
7

This gives me an error of 1004: Method 'Range' of object'_Global' failed. – Neuner Dec 21 '20 at 19:31
@Neuner, i changed the line to reflect your sheet-Object (Sheets(1) -> Sheets("Est Dtl")) – JollyRoger Dec 21 '20 at 19:43
Yes, I had done the same but it still gives me the error and I don't know why. Just to make sure, I re-copied your change and still get an error. – Neuner Dec 21 '20 at 20:23
At what rownumber is your "BorderLastRow"? I get an error 1004, when it is at Row 4 or less, wich makes sense as you define "BorderLastRow" - 4 – JollyRoger Dec 21 '20 at 20:44
BorderLastRow is currently at Row 771 – Neuner Dec 21 '20 at 20:46
I have no doubt, just wondering why mine is so messed up. This should be so simple. Do you know of a better way to make Column A dynamic between the two rows? Every other solution I've found requires there to be something in the cell. A lot of mine are blank. – Neuner Dec 21 '20 at 21:25

score 1 · Answer 2 · 2020-12-22T05:04:17.870

I’d like to provide an alternative to the given answer. The code below sets your “RangeToConsider” by first assigning variables to the top and bottom of the range; provides an error trap which tests whether the range is valid or not; and provides the ability to return font to black – should a cell’s value change from text back to numeric. Please give it a try.

Option Explicit
Sub DetailHide()
Dim shtSrc As Worksheet, RangeToConsider As Range, c As Range, t As Long, b As Long

Set shtSrc = Sheets("Est Dtl")

'Define the top (t) and bottom (b) rows of RangeToConsider
t = shtSrc.Range("EstimateDetailHeader").Row + 1
b = shtSrc.Range("BorderLastRow").Row - 1

'Test whether RangeToConsider is a valid range - exit if not
If (b - t) < 2 Then
    MsgBox "Range to consider is less than 1 - exiting sub"
    Exit Sub
End If

Set RangeToConsider = shtSrc.Range(shtSrc.Cells(t, 1), shtSrc.Cells(b, 1))

For Each c In RangeToConsider

    If IsNumeric(c.Value) = False Then
        c.Font.ThemeColor = xlThemeColorDark1
        ElseIf IsNumeric(c.Value) = True Then '<~~ delete this and next line if you wish
            c.Font.ColorIndex = xlAutomatic
    End If

Next c

End Sub

Thank you! That worked really well. I like the added error check — Neuner, Dec 22 '20 at 14:41

How to set Column A Between Two Named Rows as a Range?

2 Answers2