Need help - Identifying lastrow starting from middle of data Excel VBA

[Cakz Primz]

Dear all,

I need to fill up in column A, with "BX", based on column B until the lastrow.
The problem is currently in column A, starting from row # 2 until row # 50,000 is filled up with "AX", but the range is dynamically change.
How to fill up with "BX" word, starting from the lastrow in column A

I try to used this code, but is not working:

    Dim ws2 As Worksheet
    Set ws2 = ActiveSheet
    Dim lRow2 As Long
    lRow2 = Cells(Rows.Count, 2).End(xlUp).Row

    With ws2
    .Range(("A800000").End(xlUp).Offset(1,0) & lRow2).Value = "BX"
    End With

I can filled up with this code below, but it's not dynamic. The number of row in column B is always changing from day to day.
And is the right/proper solution.

Sub Macro11()
    Range("A135528").Select
    Selection.AutoFill Destination:=Range("A135528:A161857")
End Sub

Thank you for your kind assistance.
Regards,
Prima Indonesia

 

[Legacy 143009]

Maybe it is not the best practice but you may clear up column A and fill it again on each Worksheet Change Event. Example:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B:B") Is Nothing Then
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContent
 
   'Fill Colunm A again with your code

  End If
End Sub

 

[HaHoBe]

Hi Prima Indonesia,

what about

    With ActiveSheet
      .Range("B2", .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1)).Value = "BX"
    End With

Edit:
Disregard the above (I misunderstood) and try instead

    With ActiveSheet
      On Error Resume Next
      .Range(.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0), _
          .Cells(.Rows.Count, "B").End(xlUp).Offset(0, -1)).SpecialCells(xlCellTypeBlanks).Value = "BX"
      If Err <> 0 Then
        MsgBox "No empty cells found"
        Err.Clear
      End If
    End With

Ciao,
Holger

 

[Cakz Primz]

Maybe it is not the best practice but you may clear up column A and fill it again on each Worksheet Change Event. Example:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B:B") Is Nothing Then
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContent
 
   'Fill Colunm A again with your code

  End If
End Sub

Dear HaHoBe,

Perhaps I could not give you the proper explanation, and I am really sorry because my English is very poor.
But this picture attached, will give you the right situation.

I want to fill up, column A with "BX" word, starting from row # 161764 until the last row, based on column B.
But starting row # of "BX" could be change in daily basis. It is dynamic.

Thank you very much for your helping hand.
Prima Indonesia

 

[Cakz Primz]

Maybe it is not the best practice but you may clear up column A and fill it again on each Worksheet Change Event. Example:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B:B") Is Nothing Then
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContent
 
   'Fill Colunm A again with your code

  End If
End Sub

Dear Flashbond,

Thank you very much for your assistance. Let me give you the right situation with this picture below:

I want to fill up in column A with "BX" word, starting from rows # 161764, until the last row based column B, but this row number in column A and B is dynamically change in daily basis.

I'll be very glad if you could come back to me with the answer.

Again, thank you
Prima Indonesia

 

[HaHoBe]

Hi Prima Indonesia,

from what I understand no SpecialCells would be needed here:

  With ActiveSheet
    'check if Column A shows less values than Column B
    If .Cells(.Rows.Count, "A").End(xlUp).Row < .Cells(.Rows.Count, "B").End(xlUp).Row Then
      .Range(.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0), _
          .Cells(.Rows.Count, "B").End(xlUp).Offset(0, -1)).Value = "BX"
    End If
  End With

Command will only be executed when Column B shows more entries than Column A.

Ciao,
Holger

 

[Cakz Primz]

Hi Prima Indonesia,

from what I understand no SpecialCells would be needed here:

  With ActiveSheet
    'check if Column A shows less values than Column B
    If .Cells(.Rows.Count, "A").End(xlUp).Row < .Cells(.Rows.Count, "B").End(xlUp).Row Then
      .Range(.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0), _
          .Cells(.Rows.Count, "B").End(xlUp).Offset(0, -1)).Value = "BX"
    End If
  End With

Command will only be executed when Column B shows more entries than Column A.

Ciao,
Holger

Dear Holger,

Thanks for everything, it's perfect.
The code is working. It's a kind of magic to me.

Thank you very much
I really appreciate it.

Prima Indonesia

 

[HaHoBe]

Hi Prima Indonesia,

glad we could help solve your problem, thanks for the feedback.

Holger