CODEWITHSUNDEEP
excelvba
rainbowthug
rainbowthug

Reputation: 77

If Not <variable> Is Nothing Then set value from second row to the last

I have a problem stated in subject

My best is

If Not <variable> Is Nothing Then <variable>.EntireColumn.Value = "Customer"

so for example

If Not C Is Nothing Then C.EntireColumn.Value = "Customer"

However it changes entire column, so the first row as well, which is header. My goal is to do that from second row to avoid header change, to the last available, not empty row in this column

Hope anyone can help me on that

I've tried something like

CellsToAnonimize = Range(Cells(C.Row, C.Column), Cells(C.Row, C.Column)).End(xlDown)

For Each Cell In CellsToAnonimize
    Cell.Value = "Customer"
    
    Next Cell

Unfortunately it does not work

Upvotes: 0

Views: 75

Answers (1)

jamheadart
jamheadart

Reputation: 5313

Try this:

Range(Cells(2, c.Column).Address, Cells(Cells(Rows.Count, c.Column).End(xlUp).Row, c.Column).Address).Value2 = "Customer"

This can be broken down into more readable code with an example for column "B" like so:

Sub e()

Dim rng As Range
Dim lRow As Long
Dim c As Long

Set rng = Range("B1") ' set example for column "B"

If Not c Is Nothing Then
    
    ' get column number into variable `c`
    c = rng.Column
    
    ' get last row into `lRow`
    lRow = Cells(Rows.Count, c).End(xlUp).Row

    ' start at row 2 down to lRow for column c
    Range(Cells(2, c).Address, Cells(lRow, c).Address).Value2 = "Customer" 
End If

End Sub

Upvotes: 1

Related Questions