set range

[atbenline]

Hi, I have hit a wall trying to create a macro to set a range using the last column with data and column "C". Column "C" being the constant and the other column always changing.

Thanks for any help,

Tyler

 

[CSmith]

The macro code?

 

[atbenline]

I am currently changing the "X" value manually before I run the macro. I would like to have the macro select the last column without having to modify the macro.

Range("X:X,C:C").Select

 

[Fluff]

Maybe

Sub atbenline()
   Dim UsdCols As Long, UsdRws As Long
   Dim MyRange As Range
   
   UsdCols = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column
   UsdRws = Range("C" & Rows.Count).End(xlUp).row
   Set MyRange = Union(Range("C1:C" & UsdRws), Cells(1, UsdCols).Resize(UsdRws))
End Sub

 

[atbenline]

Thank you for your response but I am not having any luck with that.

 

[Fluff]

In what way?

 

[atbenline]

It is not selecting the columns to generate a range? It is not selecting the columns necessary to complete conditional formatting I am trying to achieve. It is skipping to hiding of the last column "X:X". I probably should have been more specific in what it is I am trying to get done.

 Dim UsdCols As Long, UsdRws As Long
    Dim MyRange As Range
    UsdCols = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column
    UsdRws = Range("C" & Rows.Count).End(xlUp).Row
    Set MyRange = Union(Range("C1:C" & UsdRws), Cells(1, UsdCols).Resize(UsdRws))
    Range("C1").Activate
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13158625
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("X:X").Select
    Selection.EntireColumn.Hidden = True

Thanks

 

[Fluff]

What exactly are your trying to do?

 

[atbenline]

Select column "C" and the last column. Highlight duplicate vales in those two columns. Then hide the last column.

 

[Fluff]

In that case everywhere you have "Selection" replace it with MyRange