Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
how to find first empty cell in a selected column ie A12:A20 with a macro
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
This should do it. Hope this helps! If so, let me know, click "YES" below.
Sub FindFirstEmptyCell() MsgBox Range("A12:A20").End(xlDown).Offset(1) End Sub -- Cheers, Ryan "plugger" wrote: how to find first empty cell in a selected column ie A12:A20 with a macro |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
You can use the End property of a range- this is the same as holding down
Ctrl + [Down/Up Arrow]. If you are sure that there is one continuous range of non-blank cells in the column then it is more reliable to start at the bottom of the column and search up: Sub GetBlankCell_1() Dim lRow As Long, lColToCheck As Long '''find first empty row in column A lColToCheck = 1 'Column A 'check last row in this column If Cells(Rows.Count, lColToCheck).Formula "" Then 'assume no empty cells in this column lRow = Rows.Count Else lRow = Cells(Rows.Count, lColToCheck).End(xlUp).Row + 1 End If 'now do something with this value Cells(lRow, lColToCheck).Select End Sub If it is better to start at the top of the column and search down, then use this: Sub GetBlankCell_1() Dim lRow As Long, lColToCheck As Long '''find first empty row in column A lColToCheck = 1 'Column A 'check last row in this column If Cells(Rows.Count, lColToCheck).Formula "" Then 'assume no empty cells in this column lRow = Rows.Count Else lRow = Cells(Rows.Count, lColToCheck).End(xlUp).Row + 1 End If 'now do something with this value Cells(lRow, lColToCheck).Select End Sub In both examples, if the column is full then the last cell in the column is selected. Cheers, Dave "plugger" wrote: how to find first empty cell in a selected column ie A12:A20 with a macro |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
On Feb 18, 9:25*am, plugger wrote:
how to find first empty cell in a selected column ie A12:A20 with a macro This will find the empty cell no matter what range you've selected. Not just limited to A12:A20. Sub FirstEmptyCell() Dim rngTest As Range, rngCell As Range Set rngTest = Application.Selection For Each rngCell In rngTest If IsEmpty(rngCell) Then MsgBox rngCell.Address & " is the first empty cell" Exit Sub End If Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
First off, since the cell will be empty, nothing will be displayed in the
MessageBox. I'm guessing you accidentally left off a reference to the address property (.Address). However, your approach might not always produce the correct result. For example, what do you get if all the cells in A12:A20 have entries in them *except* for A13 and a15? -- Rick (MVP - Excel) "Ryan H" wrote in message ... This should do it. Hope this helps! If so, let me know, click "YES" below. Sub FindFirstEmptyCell() MsgBox Range("A12:A20").End(xlDown).Offset(1) End Sub -- Cheers, Ryan "plugger" wrote: how to find first empty cell in a selected column ie A12:A20 with a macro |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
Try this code (return the row instead of the address if that is what you
want)... On Error Resume Next BlankCellAddress = Selection.SpecialCells(xlCellTypeBlanks)(1).Addres s On Error GoTo 0 -- Rick (MVP - Excel) "plugger" wrote in message ... how to find first empty cell in a selected column ie A12:A20 with a macro |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in excel
I should have mentioned that the empty string would be returned by my posted
code if there were no blanks in the selected range. If you change the variable to BlankCellRow (and Dim it as Long) and return the Row property to the variable, then BlankCellRow would be set to zero if there were no blanks in the selected range. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Try this code (return the row instead of the address if that is what you want)... On Error Resume Next BlankCellAddress = Selection.SpecialCells(xlCellTypeBlanks)(1).Addres s On Error GoTo 0 -- Rick (MVP - Excel) "plugger" wrote in message ... how to find first empty cell in a selected column ie A12:A20 with a macro |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Making excel macros run Word macros | Excel Programming |