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

Cakz Primz

Board Regular
Joined
Dec 4, 2016
Messages
102
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
    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.
Code:
Sub Macro11()
    Range("A135528").Select
    Selection.AutoFill Destination:=Range("A135528:A161857")
End Sub

Thank you for your kind assistance.
Regards,
Prima Indonesia
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Maybe it is not the best practice but you may clear up column A and fill it again on each Worksheet Change Event. Example:
VBA Code:
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
 
Upvote 0
Hi Prima Indonesia,

what about

VBA Code:
    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
VBA Code:
    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
 
Last edited:
Upvote 0
Maybe it is not the best practice but you may clear up column A and fill it again on each Worksheet Change Event. Example:
VBA Code:
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.

wonderful.png


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
 
Upvote 0
Maybe it is not the best practice but you may clear up column A and fill it again on each Worksheet Change Event. Example:
VBA Code:
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:
wonderful.png


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
 
Upvote 0
Hi Prima Indonesia,

from what I understand no SpecialCells would be needed here:
VBA Code:
  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
 
Upvote 0
Solution
Hi Prima Indonesia,

from what I understand no SpecialCells would be needed here:
VBA Code:
  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
 
Upvote 0
Hi Prima Indonesia,

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

Holger
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top