Find first empty cell and copy in formulas

kmham

New Member
Joined
Mar 6, 2008
Messages
40
Office Version
  1. 365
Platform
  1. Windows
My users input manual data on the sheet named "Log" into columns A-I. When new entries are added to the bottom, the table automatically grows. The table is named "tbl_Log" and starts in Cell A5 and currently ends on Cell AN15 (but will continue to grow every day new entries are made). Users may input 1 new row, 2, 3, etc. Will vary every day.

I'm trying to get this marco/VBA code to go to the first empty cell in Column K (Range("tbl_Log[ContractID]") - cell K14 is the first empty cell currently (sorry, I which I could upload my test file as I think that would be easier).

Then I need the macro to determine how many blank rows are in the table (currently there are 2 empty rows determined by Cells K14 and K15 being empty). I also have a formula on the tab named "Lists" in cell S4 that provides the # of blank rows...it's simply =Countblank(tbl_Log[ContractID]). I'm assuming this could be built into the marco/VBA code?

Now knowing that we're dealing with 2 empty rows, I need to copy vlookup formulas from the tab "Lists" cells Q9:AT9 (also a defined named range called "rng_Vlookups") into cells K14:AN15. After the vlookup formulas are copied in, I then want to do a Copy-Paste-Special-Values to hard code the data. And then I'd like to simulate hitting the HOME key so the user is taken to the far left of the spreadsheet on the row they are on.

Ideally I would like the code to tell a user if no empty cells/rows are found in Column K (specifically within the column named "ContractID"). I suppose a message to tell them that they successfully copied in 2 rows would be good too, but certainly not necessary.

Appreciate any help in getting this working. My current code is below:

VBA Code:
Sub CopyVlookups()
'
' CopyVlookups Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Sheets("Lists").Select
    Application.Goto Reference:="rng_Vlookups"
    Selection.Copy
    Sheets("Log").Select
    Range("tbl_Log[[#Headers],[ContractID]]").Select
    Selection.End(xlDown).Select
    Range("K14:K15").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A14").Select  'I really want this to be the equivalent of hitting the HOME key, not jumping to A14, but instead jump to the far left of whatever row you're on.
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,147,960
Messages
5,744,047
Members
423,843
Latest member
alex2022

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