Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default 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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: VBA code to find first blank cell in a column and activate it

Sure, I can help you with that! Here's a VBA code that will find the first blank cell in column A and activate it:

Formula:
Sub FindFirstBlankCell()
    
Dim LastRow As Long
    LastRow 
Cells(Rows.Count"A").End(xlUp).Row 'finds the last row with data in column A
    
    For i = 1 To LastRow '
loops through each row in column A
        
If Cells(i"A").Value "" Then 'checks if the cell is blank
            Cells(i, "A").Select '
selects the first blank cell
            
Exit For 'exits the loop once the first blank cell is found
        End If
    Next i
End Sub 
To use this code, open your Excel workbook and press Alt + F11 to open the VBA editor. Then, click on Insert Module and paste the code above into the module. Finally, press F5 to run the macro.

This code will find the first blank cell in column A and activate it, so you can paste your data into it. If you need to find the first blank cell in a different column, simply change the "A" in the code to the appropriate column letter.
  1. Open Excel workbook
  2. Press Alt + F11 to open the VBA editor
  3. Click on Insert Module
  4. Paste the code into the module
  5. Press F5 to run the macro
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default VBA code to find first blank cell in a column and activate it

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" wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,533
Default VBA code to find first blank cell in a column and activate it

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" skrev i meddelelsen
...
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.


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default VBA code to find first blank cell in a column and activate it

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.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default VBA code to find first blank cell in a column and activate it

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to find blank cell in column vinod New Users to Excel 1 April 3rd 09 08:27 PM
Trying to find the fourth blank cell in a column teepee[_3_] Excel Discussion (Misc queries) 7 April 28th 08 12:09 AM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
Find 1st blank cell in column & sum to the same row in another col Sharon Excel Worksheet Functions 2 March 7th 07 03:00 AM
Activate cell in column B after performing Find excelnut1954 Excel Programming 2 July 6th 06 07:08 PM


All times are GMT +1. The time now is 01:40 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"