If cell B is not empty, drag down formulas in columns A-N

MMR

New Member
Joined
Oct 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi there,
I was hoping you could help with the following:
  • I have an Excel worksheet, with formula data within columns A-N
  • I have IDs/Names in column B - sometimes there are more Names and sometimes there may be less (I paste in data daily via an already existing Macro)
  • On the days where there are less, I would like a Macro to look at column B, go down all the way to the bottom to find the last cell containing a name/ID (e.g. cell B322); if this is not blank (i.e. cell in column B is populated with a name/ID), then drag down the formulas in columns A and C-N accordingly to fill in the rest of the data for that ID/Name
  • As an example, on the other hand, on days where there is less data, I have written a short Macro to identify the bottom of column B with populated data, and if this is blank, to delete the whole rows (to remove the excess formulas that are not needed from previous day) - the code below is working fine (so I just need a macro for the alternative option above):
Worksheets("Portfolio").Select
Range("B2:B15000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub mmr()
   Dim LrA As Long, LrB As Long
   
   LrA = Range("A" & Rows.Count).End(xlUp).Row
   LrB = Range("B" & Rows.Count).End(xlUp).Row
   If LrA < LrB Then
      Range("A" & LrA).Resize(LrB - LrA + 1).FillDown
      Range("C" & LrA).Resize(LrB - LrA + 1, 12).FillDown
   ElseIf LrA > LrB Then
      Range("A" & LrB + 1).Resize(LrA - LrB, 14).ClearContents
   End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,590
Messages
5,625,674
Members
416,125
Latest member
NeedExcelHelp2021

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
Top