Exclude first row (it is row #1 in my sheet) from USEDRANGE method?

Question

I have some input data from my broker. I've written some code to automate calculations, adding columns and inserting some formulas.

In the end, I want to do some conditional formatting (affecting whole rows) to determine transactions that are profitable (green font whole row) and transactions that are lost (red font whole row).

To do this I've used USEDRANGE method - I know it's a tricky one, by my data are consistent - there's no empty rows, only few empty columns, so I think USEDRANGE will handle it. I need to use USEDRANGE because I'll have more rows next time I'll run this report.

But I have in my data the first row, where I keep headers 4 my columns.

I want my headers to stay black (font), but I still want to use USEDRANGE method.

How do I execute my conditional formatting using USEDRANGE method, excluding the first row (so it stays black font).

Option Explicit
Dim RowNumber As Long
Dim LastRow As Long
Dim ColumnNumber As Integer
Dim LastColumn As Integer
Dim VBA As Worksheet
Dim TotalRange As Range

Sub CondicionalFormating_WholeRows()
    Set VBA = Workbooks("lista transakcji Dukascopy od October 2015.xlsm").Worksheets("VBA")
    Set TotalRange = VBA.UsedRange
    LastRow = VBA.Cells(Rows.Count, 1).End(xlUp).Row
    LastColumn = VBA.Cells(1, Columns.Count).End(xlToLeft).Column

TotalRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$H1<0"
TotalRange.FormatConditions(TotalRange.FormatConditions.Count).SetFirstPriority
    With TotalRange.FormatConditions(1).Font
        .Bold = False
        .Italic = False
        .Strikethrough = False
        .Color = -16777024
        .TintAndShade = 0
    End With
    TotalRange.FormatConditions(1).StopIfTrue = False

TotalRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$H1>0"
TotalRange.FormatConditions(TotalRange.FormatConditions.Count).SetFirstPriority
    With TotalRange.FormatConditions(1).Font
        .Bold = False
        .Italic = False
        .Strikethrough = False
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.499984740745262
    End With
    TotalRange.FormatConditions(1).StopIfTrue = False

    '   VBA.Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
End Sub

I suggest you don't use `.UsedRange` and explicitly set `TotalRange`. `.UsedRange` can cause headaches. — Kyle, Aug 25 '16 at 20:47
First, I would do Resize. And only then Offset. Otherwise, error 1004 is possible. — 111111, Apr 04 '18 at 09:55

Tim Williams · Accepted Answer · 2016-08-25T20:06:40.370

19

Set TotalRange = VBA.UsedRange '<<< your existing line

'Add this line right after
Set TotalRange = TotalRange.Offset(1,0).Resize(TotalRange.Rows.Count-1, _
                                               TotalRange.Columns.Count)

edited Aug 25 '16 at 20:06

answered Aug 25 '16 at 19:54

Tim Williams

154,628
8
97
125

should I just add this second line 2 my code? can U comment a little on that part: ... ...Resize(TotalRange.Rows.Count-1, TotalRange.Columns.Count) – michal roesler Aug 25 '16 at 20:01

score 10 · Answer 2 · answered Aug 25 '16 at 20:34

Use .offset(1) to move the entire range reference down 1 row. This will leave an empty row at the end of the range. .Resize(VBA.UsedRange.Rows.Count - 1) will trim off the last rows.


Set TotalRange = VBA.UsedRange.Offset(1).Resize(VBA.UsedRange.Rows.Count - 1)

Exclude first row (it is row #1 in my sheet) from USEDRANGE method?

2 Answers2