Autofill from Last row of a column till Last row of the entire sheet-CodePudding

Here I want to auto fill from B6 to B10. B5 is the last row of Column B and A10 is the last row of entire sheet.

Currently i am using the following code.

Dim Lastrow As Long
Dim Last As Long

Application.ScreenUpdating = False


Lastrow = Range("A" & Rows.Count).End(xlUp).row    'Last row from entire sheet

Last = Range("B" & Rows.Count).End(xlUp).Offset(1).row      'Last row of Column B  1

Range(Last & Lastrow).Formula = "4"


Application.ScreenUpdating = True

This works if i am using

Range(B6:B & Lastrow).Formula = "4"

But This might not be helpful if the last rows changes

CodePudding user response：

Range(B6:B & Lastrow).Formula = "4"

Change to

Range("B" & last & ":B" & Lastrow) = "4"

CodePudding user response：

The next code will do what you required, referring the real last cell of the sheet, independent of the column where it exists:

  Dim LastrowSh As Long, LastB As Long

  LastrowSh = ActiveSheet.cells.SpecialCells(xlCellTypeLastCell).Row 'Last row from entire sheet
  LastB = Range("B" & rows.count).End(xlUp).Offset(1).Row            'Last row of Column B   1

  Range("B" & LastB, "B" & LastrowSh).value = "4"