CODEWITHSUNDEEP
excelvba
michal roesler
michal roesler

Reputation: 499

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

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.

enter image description here

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

Upvotes: 11

Views: 35980

Answers (2)

user6432984
user6432984

Reputation:

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)

Upvotes: 10

Tim Williams
Tim Williams

Reputation: 166181

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)

Upvotes: 19

Related Questions