Solved: execute code on the lines with data in column "B"

[marreco]

I need to count how many rows have data in column "B" and then make the code run somenete lines with data?


Public Sub Change()

Dim rngCelula As Range
Dim LR As Long: LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("B:B").Select
For Each rngCelula In Selection
rngCelula.FormulaLocal = rngCelula.Value
Next rngCelula
End Sub

Thank you!!!

[Bob Phillips]

Public Sub Change()

Dim rngCelula As Range
Dim LR As Long: LR = Cells(Rows.Count, 1).End(xlUp).Row

For Each rngCelula In Range("B2").Resize(LR - 1)

'run some code on rngCelula
Next rngCelula
End Sub

[marreco]

I tried it but did not work

Public Sub Change()

Dim rngCelula As Range
Dim LR As Long: LR = Cells(Rows.Count, 1).End(xlUp).Row

For Each rngCelula In Range("B2").Resize(LR - 1)

'run some code on rngCelula
Next rngCelula
End Sub


Private Sub CommandButton1_Click()
Dim nRow As Long, nLastRow As Long

With ThisWorkbook.Worksheets("Base")
nLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For nRow = 2 To nLastRow
If .Cells(nRow, "A") = ComboBox1.Text Then
.Cells(nRow, "B") = TextBox1.Value
End If
Next nRow
End With
Call Change
End Sub

[Bob Phillips]

What doesn't work? In what way? What should happen?

[marreco]

I need to run code "Public Sub Change ()" after hitting a CommandButton.

the code works but it needs to go to the data in column "B" without having to go through the empty cells

[Kenneth Hobs]

Did you put the Change routine into a Module? Obviously, you need the code to do something with rngCelula as xld commented.

IF you want to check for Empty and do something if not empty, replace MsgBox with what you need.


Public Sub Change()
Dim rngCelula As Range
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row

For Each rngCelula In Range("B2").Resize(LR - 1)
'run some code on rngCelula
If Not rngCelula.Value = Empty Then MsgBox (rngCelula.Value)
Next rngCelula
End Sub

[marreco]

it solved now

Public Sub Change()
Dim rngCelula As Range
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row

For Each rngCelula In Range("B2").Resize(LR - 1)
'run some code on rngCelula
rngCelula.FormulaLocal = rngCelula.Value
Next rngCelula
End Sub