Forum Discussion

JMKnox's avatar
JMKnox
Copper Contributor
Jan 18, 2023

How to apply changes only to active rows when writing a macro

I am trying to write a macro which will insert a few blank columns and then insert specific values into the new columns (one example is simply inserting the letter "C"). This is easy to do manually, as I can just type "C" in the top row and then select the last row within the column and click. The part that I can't figure out is how to define the last row for the newly inserted values when automating the process in a macro. For example, if my current data occupied 25 rows, how do I get the data to only populate rows 1-25? I found some info on how to query the last row, but still couldn't figure out how to incorporate this into a macro.

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 18, 2023

    JMKnox 

    For example:

    Sub Test()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("D1:F1").EntireColumn.Insert
    Range("D1:F" & LastRow).Value = "C"
    Application.ScreenUpdating = True
End Sub

Resources