How do I find the next blank cell in a range?
Hey there all -
I am sure that this is terribly simple, but it has eluded me thus far. I am trying to figure out how to find the first blank cell in a range. I am working on creating a time sheet, and I need to be able to find the first blank cell in a specific range of cells. I have found information on finding the first blank cell in a single column, but this will be a two column range that I am working with. Any help would be greatly appreciated. |
How do I find the next blank cell in a range?
I'm guessing that there's more to this story....
What do you ultimately want to do after you find the blank cell? *********** Regards, Ron XL2002, WinXP "EazyCure" wrote: Hey there all - I am sure that this is terribly simple, but it has eluded me thus far. I am trying to figure out how to find the first blank cell in a range. I am working on creating a time sheet, and I need to be able to find the first blank cell in a specific range of cells. I have found information on finding the first blank cell in a single column, but this will be a two column range that I am working with. Any help would be greatly appreciated. |
How do I find the next blank cell in a range?
After finding the blank cell I am going to enter the current date and time.
In the end I am going to write a macro where with the click of a button the current date and time will be entered into the first available cell, I just got hung up trying to find the first available cell. |
How do I find the next blank cell in a range?
Say your range is A1:B9999
And both A9999 and B1 are the first blank cells in each column. Which is the first blank cell in that range (A1:B9999)? No matter what you respond, you may want to look at ..specialcells(xlcelltypeblanks).cells(1). The question is what to apply it to. It's the equivalent of selecting a range and hitting edit|goto|special|Blanks and then using the first cell in that reduced range. EazyCure wrote: After finding the blank cell I am going to enter the current date and time. In the end I am going to write a macro where with the click of a button the current date and time will be entered into the first available cell, I just got hung up trying to find the first available cell. -- Dave Peterson |
How do I find the next blank cell in a range?
First blank would be B1. (Left to right, then top to bottom).
|
How do I find the next blank cell in a range?
With the range selected, choose EditFind [blank] to locate the next
blank cell. On 11 Jun, 17:41, EazyCure wrote: After finding the blank cell I am going to enter the current date and time. In the end I am going to write a macro where with the click of a button the current date and time will be entered into the first available cell, I just got hung up trying to find the first available cell. |
How do I find the next blank cell in a range?
You can loop backwards through the columns:
Option Explicit Sub testme01() Dim FirstEmptyCell As Range Dim wks As Worksheet Dim myRng As Range Dim iCol As Long Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1:B20") End With With myRng For iCol = .Columns.Count To 1 Step -1 Set FirstEmptyCell = Nothing On Error Resume Next Set FirstEmptyCell _ = .Columns(iCol).Cells.SpecialCells(xlCellTypeBlanks ).Cells(1) On Error GoTo 0 If FirstEmptyCell Is Nothing Then 'keep looking Else Exit For End If Next iCol End With If FirstEmptyCell Is Nothing Then MsgBox "No empty cells!" Else MsgBox FirstEmptyCell.Address End If End Sub Be aware that .specialcells() only looks at the used range. I'm not sure if that's a problem for you. EazyCure wrote: First blank would be B1. (Left to right, then top to bottom). -- Dave Peterson |
All times are GMT +1. The time now is 09:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com