VBA code to find first blank cell in a column and activate it

MS XP Pro, MS Office 2007.
Can anyone assist with macro to find the first blank cell in a column and 
then to make it the active (selected) cell so that data can be pasted to it 
and the row in which it is placed. 
I cant find a stable and reliable solution with the Find Blank Cell 
procedure in the menu.
0
Utf
2/28/2010 8:46:01 AM
excel.programming 6508 articles. 1 followers. Follow

4 Replies
14326 Views

Similar Articles

[PageSpeed] 13

When you say first blank cell, do you mean  -
From the top down, the first blank cell with possibly other non blank cells 
below that blank cell.
or -
From the bottom up, the cell below the first non-blank cell.

Regards,
Peter T

"Wes_A" <WesA@discussions.microsoft.com> wrote in message 
news:06D72679-69DB-4591-A520-80CA75F71B2A@microsoft.com...
> MS XP Pro, MS Office 2007.
> Can anyone assist with macro to find the first blank cell in a column and
> then to make it the active (selected) cell so that data can be pasted to 
> it
> and the row in which it is placed.
> I cant find a stable and reliable solution with the Find Blank Cell
> procedure in the menu. 


0
Peter
2/28/2010 9:09:46 AM
Dim FirstBlankCell as Range
Set FirstBlankCell=Range("A" & rows.Count).end(xlup).offset(1,0)
FirstBlankCell.Activate

Note that you do not need to select a cell to paste data into it, and it is 
slowing down your macro.

Look at this:

Worksheets("Sheet2").Range("A2:D2").copy Destination:=FirstBlankCell

Regards,
Per

"Wes_A" <WesA@discussions.microsoft.com> skrev i meddelelsen 
news:06D72679-69DB-4591-A520-80CA75F71B2A@microsoft.com...
> MS XP Pro, MS Office 2007.
> Can anyone assist with macro to find the first blank cell in a column and
> then to make it the active (selected) cell so that data can be pasted to 
> it
> and the row in which it is placed.
> I cant find a stable and reliable solution with the Find Blank Cell
> procedure in the menu. 

0
Per
2/28/2010 9:11:00 AM
How about:

Sub FindFirstBlank()
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("B:B"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
    r2.Select
Else
    r1.Select
End If
End Sub
-- 
Gary''s Student - gsnu201001


"Wes_A" wrote:

> MS XP Pro, MS Office 2007.
> Can anyone assist with macro to find the first blank cell in a column and 
> then to make it the active (selected) cell so that data can be pasted to it 
> and the row in which it is placed. 
> I cant find a stable and reliable solution with the Find Blank Cell 
> procedure in the menu.
0
Utf
2/28/2010 2:24:01 PM
This is a correction:

Sub FindFirstBlank()
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("B:B"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
    r2.Select
Else
    r1(1).Select
End If
End Sub

-- 
Gary''s Student - gsnu201001


"Wes_A" wrote:

> MS XP Pro, MS Office 2007.
> Can anyone assist with macro to find the first blank cell in a column and 
> then to make it the active (selected) cell so that data can be pasted to it 
> and the row in which it is placed. 
> I cant find a stable and reliable solution with the Find Blank Cell 
> procedure in the menu.
2
Utf
2/28/2010 2:28:01 PM
Reply:

Similar Artilces:

How to Stop Worksheet_Change event from running during code execution
I'm sorry If I've asked this before but I can't find an answer to it. I have the following 2 events in a worksheet module.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) Private Sub Worksheet_SelectionChange(ByVal Target As Range) I run some codes, either in UserForms or general procedures that make changes to this worksheet. Q. Is there a way to by-pass those two events somehow? As an example I have a uf that comes up if a cell is selected in that sheet. So I need those worksheet_Change events to work at that point. But then, the uf does some stuff like inputing ...

Changing Rows to Columns
Is it possible to easily rotate a spreadsheet or part of a spreadshet, to change the data in rows to columns? Do to a sorting problem in the charts I create, it seems I have to sort the data in the spreadsheet first. Any help would be appreciated. Hi, Sure is a way. select & copy your data. then select the cell where you want to re-paste; right-click and select paste special; in the dialog box check "transpose". Ok. jeff >-----Original Message----- >Is it possible to easily rotate a spreadsheet or part of a >spreadshet, to change the data in rows to columns? D...

VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...

Payout reason codes
Can someone point me to where in the database the payout reason codes are stored? thanks, Store Ops Manager, Database, Reason Codes, Type = Payout "Jason" wrote: > Can someone point me to where in the database the payout reason codes are > stored? > > thanks, Thanks. What I meant (and asked poorly) was if I wanted to design a custom report to show that info where would I need to look? Thanks, "Wendy_I" wrote: > Store Ops Manager, Database, Reason Codes, Type = Payout > > > > "Jason" wrote: > > > Can someo...

Sort several workbooks using code
I am looking to run code that will sort several workbooks even when they are closed. The code would be executed via a master workbook which will also be sorted. Is this possible? I'm not aware of sorting records in a CLOSED workbook, but what you can do is this. Create some button on the master workbook and let it open the other workbook, sort the other workbook and close the other workbook. Accompanied with Application.ScreenUpdating (but also depending on the amount of records) you will hardly notice the sorting actions. Does this help ? -- Regards; Mark ...

Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...

Colour Grouping of Columns
Excel 97 Is there a way to colour the columns in a chart depending on their numerical range. For example: Say we have a series of values from 0 through 100 represented in the column chart. I would like 0-25 to be one colour, 26-50 to be another, etc etc. Is this possible? Any advice gratefully appreciated. WSF WSF, Yes, you can do so by setting up various ranges with IF statements and multiple culumns. Please see Jon Peltier's discussions on conditional charting: http://www.peltiertech.com/Excel/Charts/ConditionalChart1.html http://www.peltiertech.com/Excel/Charts/ConditionalC...

Find e-mail account info.
I need to know how to get the pop3 and stmp info from both a hotmail and msn account to set up my mail retrevial in outlook. You cannot pop email from hotmail, you need to use HTTPMail. When you setup an account, use the HTTP account type. >-----Original Message----- >I need to know how to get the pop3 and stmp info from both >a hotmail and msn account to set up my mail retrevial in >outlook. >. > ...

Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...

Jump to the item in a list as the first letter is pressed
I have couple of large lists with alphabetised data. Is it possible to have intellisense, like if P is pressed on the keyboard, it should jump to P, if H then jump to first name with H as the first letter, like that. It does not happen with the lists I have(by default)?Is this possible? Thnx Here is one way you could try, but it will go there and filter at th same time (you may or may not want it) :shock: If your list is in contiguous rows and columns, meaning no blank rows no blank columns, then setup autofilter. The dropdown list accept first letter Autofilter is setup using toolbar&...

VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

How get rid of reveal codes?
How get rid of reveal codes? the symbols are all over my document!! How do I get rid of them?? Try:- ALT + F9 If my comments have helped please hit Yes. Thanks. "Christa" wrote: > How get rid of reveal codes? the symbols are all over my document!! How do > I get rid of them?? Ctrl+* or Ctrl+Shift+8. See http://word.mvps.org/FAQs/Formatting/NonPrintChars.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Christa" <Christa@discussions.microsoft.com> wrote in mes...

vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

DevCon
I'm running Win 7 64-bit on an AMD processor. Most times, when I boot, my network adapter fails to initialize properly. I've found if I disable and enable it through the device manager, it will work fine from then on. To automate this, I picked up the WDK and DevCon.exe, and thought I'd set up a batch file to prep the adapter after boot. I can determine status as follows: C:\WinDDK\7600.16385.1\Tools\devcon\amd64>devcon status =net *81411043* PCI\VEN_10DE&DEV_0057&SUBSYS_81411043&REV_A3\3&2411E6FE&1&50 Name: NVIDIA nForce Networking Co...

Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...

get change operation on coCustomLinks cannot find table
Hello, I have this client that has deployed their server in Singapore but has a branch in Thailand. Since Thailand also needs to use Great Plains, they have installed a GP 8.0 application in a workstation in Thailand and connects to the database deployed in Singapore. But when the staff in thailand logs onto her workstation, she gets the error "get/change first operation on coCustomLinks cannot find table" then all suceeding document processing (i.e. GL entries), go haywire - the next journal entry number doesn't generate. They went through the same installation procedures ...

Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...

How do I protect specific cells from changes in Excel?
Please can someone help me. How do I protect specific cells from changes in Excel? I have a spreadsheet that I would like users to only be able to change certain cells, the others need to be protected from change Go to Format>Cells. On the Protection tab, uncheck the "Locked" box for any cell you want to be editable. After you protect the document, the user will only be able to edit the cells you unlock. "LarryH" <LarryH@discussions.microsoft.com> wrote in message news:2E8A5FD8-A990-4181-9842-19B007C88F71@microsoft.com... > Please can someone help me. ...

Referencing dates against Products, in order to find the Qty. With out using Column referencing
if i have 1 sheet which has the master data for all customers and then i have several workbooks with customer sales Master sheet B2 Sales Date - this row has every day of the year A3 product B3 is sales Qty 1 1 2 55 3 55 Customer sheet has a similar layout but because this is made from a pivot table its only based on sales so when it comes to christmas day when there are no sales i cannot simply do a vlookup using column references as my data will be out of sync How do i reference the product and the date ...

What's the best way to find out if user is currently using mailbox ?
on Exch2000Sp3, I go to Systems Manager and I see mydomain\certain user mailbox. Last logoff time = blank. As an example, I compare my logoff information: my Outlook is currently opened but my logoff time = 10/07/04, 9:00AM Are those info reliable indicators of current mailbox activity ? Please advise. If policy allows, examine the sent items folder. -- regards, Michael Abbaticchio MVP for Microsoft Exchange Server http://mvps.org/exchange "Marlon Brown" <marlon_brown@hotmail.com> wrote in message news:%23%23LdPdMrEHA.4008@TK2MSFTNGP14.phx.gbl... > on Exch2000Sp...

Excel 2000... Missing row and column designators. #3
...

Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...

Pls assist How 2 find Mystery in complex pivottable data source base on access
Hi there, I was asked to maintain a complex system in which pivottable are base on access object (MDB) I went into the wizard returned to the fields making the pivottable Went through all the queries and tables in the MDB but could not find out where the pivottable (excel) is getting the (wrong) data. Is there a way to see the SQL which makes the pivottable?? Any advice will be appreciated You could run a macro to view the pivot cache's command text. For example, the following code would print the text in the Immediate window of the Visual Basic Editor: Sub ShowCommandText() 'show...

Excel and VBA
I am very interested in learning more about using VBA in office, especially Excel. Can you post some resources (online, books, etc) that can be used to help learn about using Visual Basic in office. Thanks! Here's a good place to start: http://www.contextures.com/xlbooks.html Does that help? *********** Regards, Ron XL2002, WinXP "Bob Sinclair" wrote: > I am very interested in learning more about using VBA in office, especially > Excel. Can you post some resources (online, books, etc) that can be used to > help learn about using Visual Basic in office. >...