Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select first empty cell in a range
Let's say I have a range of cells (A5:D15). I would like a VBA code that
would start at A5 and go down from A5 to A15, then start at B5 and progress to B15, then C5 - C15 etc. until it finds the first empty cell. Please help. -- Thanks Shawn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select first empty cell in a range
This will loop through the cells in a the range going A5, B5, C5, D5,
A6, B6 etc until it finds an empty cell. Sub lprng() Dim cell As Range For Each cell In Range("A5:D15") If cell.Value = Empty Then Debug.Print cell.Address Exit For End If Next cell End Sub Or if you wanted to perform some operation on each cell in the range then: Sub lprng() Dim cell As Range For Each cell In Range("A5:D15") cell.Font.Bold = True Next cell End Sub Hope this helps Rowan Shawn wrote: Let's say I have a range of cells (A5:D15). I would like a VBA code that would start at A5 and go down from A5 to A15, then start at B5 and progress to B15, then C5 - C15 etc. until it finds the first empty cell. Please help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select first empty cell in a range
Hi Shawn,
Try: '================== Public Sub Tester() Dim WB As Workbook Dim WB As Workbook Dim SH As Worksheet Set WB = ActiveWorkbook '<<========== CHANGE Set SH = ActiveSheet '<<========== CHANGE Dim rng As Range Set rng = SH.Range("A5:D15") Dim FirstBlank As Range Dim i As Long For i = 1 To rng.Columns.Count On Error Resume Next 'In case no empty cells found! Set FirstBlank = rng.Columns(i).SpecialCells(xlBlanks)(1) On Error GoTo 0 If Not FirstBlank Is Nothing Then 'First empty cell found 'Do something e.g.: MsgBox FirstBlank.Address Exit For End If Next i If FirstBlank Is Nothing Then 'No empty cells found in designated range! 'Do something else, e.g. MsgBox "No empty cells found in " _ & rng.Address(0, 0, external:=True) End If End Sub '<<================== --- Regards, Norman "Shawn" wrote in message ... Let's say I have a range of cells (A5:D15). I would like a VBA code that would start at A5 and go down from A5 to A15, then start at B5 and progress to B15, then C5 - C15 etc. until it finds the first empty cell. Please help. -- Thanks Shawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select next empty cell | Excel Discussion (Misc queries) | |||
Function to select second-to-last non-empty cell | Excel Worksheet Functions | |||
Select variably-sized range which contains empty cells | Excel Programming | |||
Select Until Empty Cell From VBA Code | Excel Programming | |||
Macro to select non empty rows in a given range | Excel Programming |