Contents
Sometimes, You may need to find and select the first blank cell or last blank cell in a column, these macros can help you.
Find and Select the First Blank Cell in Column A
Sub Macro1()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(1).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
End Sub
or
Sub Macro2()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(1).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell
End Sub
First blank cell: before selection
First blank cell: after selection
Find and Select the Last Blank Cell in Column A
Sub Macro3() 'Step 1: Declare Your Variables. Dim LastRow As Long 'Step 2: Capture the last used row number. LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Step 3: Select the next row down Cells(LastRow, 1).Offset(1, 0).Select End Sub
or
Sub Macro4()
'Step 1: Declare Your Variables.
Dim LastBlankRow As Long
'Step 2: Capture the last used row number.
LastBlankRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
'Step 3: Select the next row down
Cells(LastBlankRow, 1).Select
End Sub
Note: Some of the most common ways of finding last row which are highly unreliable and hence should never be used:
UsedRangexlDownCountA
Why we use Rows.Count not 65536?
This question is a classic scenario where the code will fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.
How to use it
To use this macro, you can copy and paste it into a standard module:
- Activate the Visual Basic Editor by pressing ALT F11.
- Right-click the project/workbook name in the Project window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module.
to quickly delete all blank rows interspersed in a set of data, I added
Selection.EntireRow.Delete
after the Next command and assigned the macro a shortcut key
Thankyou very much for very informative article
Hello to all
i need to know what the code of below scenario would be:
multiple a1 to b1 and response appears in c1
then select the a2 automatically and continue this. at the end i have coloumn C all my results.
i hope i could explain it precisely.
thanks in advance
Sub CHEESSE()
Do While ActiveCell.Offset(0, -1) “”
ActiveCell.Value = ActiveCell.Offset(0, -2).Value * ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(1, 0).Activate
Loop
End Sub
How can I include more then one of these in a single VBA?
Thanks Rob
Hi,
How to paste the copied first cell into blank cells in the same column.
Hi,
The macro works for column A. I want to repeat this process but for columns b.c.d.e,etc
The reason is that I am copying data from my invoice to a sheet to store my data of my invoice.
Can you help, please?
How to repeat this if it has two empty cells
Love the finding next cell as below:
Sub Macro1()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(1).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
End Sub
I’m totally new to this and wish to know if you can combine with this code:
Sub Save7()
Dim NextRow As Range
Set NextRow = Range(“A” & Sheets(“Sheet1”).UsedRange.Rows.Count + 1)
Sheet2.Range(“A4:BQ7”).Copy
Sheet1.Activate
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing
End Sub
I’ve got a list of products to edit, needed to add 4 rows which i’ve managed. Now with your find next cell works well, but now need to combine to paste the required information into the 4 rows. They are all the same info for the new 4 row data. But i’ve over 2000 products i need to do it to.
I have used this code to find the first blank cell in column A…i need to find the first blank cell in Column F or others. I also have a merged cell range spanning from (B1:AF1) used as a report title with a formula(it always stops at B1).
I need to paste data in the first blank cell of column F.
Sub Macro1()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(1).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
End Sub
How do I go to the cell above the first blank cell in a certain column?
For “Find and Select the First Blank Cell in Column A”–Thank you very much! It’s a tremendous help!
Thanks for sharing the code. I’m new here, would like to know how to include formula after selecting the next empty cell in VB?
The first example produces a compile error – cell variable not defined. Am I missing something?