Ignoring Formulas When Searching For Next Empty Cell

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Hello! The following was created to find the next empty cell within a range in column B, and then paste in info which was copied from O6:P6. The only problem is that it's not working correctly because I have cells in column B which contain formulas (fitted with an IFERROR prefix to keep some cells empty when there is no info to fill them). How can I edit this to ignore cells containing formulas in column B when looking for the next empty?

Sub AddExtraStoreToLocMasterList() '
' AddExtraStoreToLocMasterList Macro
' This macro copies O6:P6 to cipboard, then finds first blank cell in column B Loc Master List range, and the paste specials there.
'
Range("O6:P6").Select
Selection.Copy
Range("B4").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O6:P6").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("O6").Select
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,980
Office Version
  1. 2016
Platform
  1. Windows
I thought you were looking to find blank cells without formulas? What exactly did you mean when you said "How can I edit this to ignore cells containing formulas in column B when looking for the next empty?"? I am kind of confused at what you are trying to do. Are you simply looking for the blank cell just below the last formula in Column B?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
I'm looking for the next visibly bank cell, after the visiby occupied cells (which show as having contents). That said, if there are no visible contents, then it will contain a formula. It's ok to paste over the formula. The reason this is not an issue is because the sheet is a one-time-use sheet, then you start from scratch again the next day with a fresh... original sheet.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,060
Messages
5,639,824
Members
417,117
Latest member
Ravi Pandey

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