Move to next empty cell in range 1

[Randy11]

Have a range of A10:B20 as an example. Am looking for code that will choose the next blank cell in this range. Would like to have this run vertically. i.e. Start looking in A10, then A11, A12 etc until A20, then to B10, B11....

This works for simple 1 column situation....
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select

Your ideas appreciated

 

[SkipVought]

hi,

dim rng as range

set rng = activehseet.cells(10,1).end(xldown).offset(1)

rrg references the next cell in column A.

Why do you need to select?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Hi Skip Am using a command button to direct an operator to a range where they will key. Did the Select because I thought it was needed.

How can I outline the range (A10:B20) in this code so it knows to start looking in cell A1?

This comes up with a object required 424 error.

Sub repair()
Dim rng As Range

Set rng = activehseet.Cells(10, 1).End(xlDown).Offset(1)

End Sub

 

[SkipVought]

sorry...

Sub repair()
    Dim rng As Range
    
    With ActiveSheet.Cells(10, 1)
        If .CurrentRegion.Rows.Count > 1 Then
            Set rng = .End(xlDown).Offset(1)
        Else
            If .Value = "" Then
                Set rng = .Cells
            Else
                Set rng = .Offset(1)
            End If
            
        End If
    End With
    rng.Select

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Hi Skip With new code am getting, Compile Error: Syntax Error. Am not sure where I am going wrong on this.... Do we not need to outline the range A10:B30?
R

Sub repair()

Red    Dim rng As Range
    
    With ActiveSheet.Cells(10, 1)
Red        If .CurrentRegion.Rows.Count > 1 Then
            Set rng = .End(xlDown).Offset(1)
        Else
Red            If .Value = "" Then
                Set rng = .Cells
            Else
              Set rng = .Offset(1)
Red            End If
            
Red        End If
Red    End With
    rng.Select

End Sub

 

[SkipVought]

Do we not need to outline the range A10:B30?

Maybe I do not understand your table. What are you doing and what kind of data in columns A & B, that you would need to...

Start looking in A10, then A11, A12 etc until A20, then to B10, B11

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Data is simple. Is $ values input to these cells starting at A10... A11, etc through B10, B11 to B20. Mutiple sheets have same structure for different units. So looking to click on button on worksheet & drop into next available cell in range A10:B20 on active sheet. Same code will be used on other worksheets with same structure.
Your assistance is much appreciated.

 

[SkipVought]

I am still confused about how this sheet will be used.

A normal table structure has rows that represent records in a table, where all the values in a row have a common relationship.

Going down column A and then column B, does not seem to have this rather important context.

So I guess that you might start out with a value in A10, and the workd is happy. Then a value in A11, and the workd is happy. And so forth until you have values in A10 thru A20 and the world is happy. Then NEXT goes a value in B10 and the world is happy.

What is this doing? What is the purpose?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Hi Skip Is just a simple input template that can be printed in a reasonable format. There is a data table else where that accumulates this data. The alternative which I will be working on will be designing a form to input data directly to the data table. In the interim, am trying to make as easy as possible for person entering info while we do a little testing to identify any improvements / features / missed items we want in the final build.

 

[SkipVought]

Sub repair()
    Dim rng As Range, iRow As Integer, iCol As Integer
    
    For iCol = 1 To 2
        For iRow = 10 To 20
            If Cells(iRow, iCol).Value = "" Then
                Cells(iRow, iCol).Select
                Exit Sub
            End If
        Next
    Next
    
    MsgBox "FULL"
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

sorry...

Sub repair()
    Dim iRow As Integer, iCol As Integer
    
    For iCol = 1 To 2
        For iRow = 10 To 20
            If Cells(iRow, iCol).Value = "" Then
                Cells(iRow, iCol).Select
                Exit Sub
            End If
        Next
    Next
    
    MsgBox "FULL"
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Compile Error: Syntax Error still? issue starts at the following in the code...
Dim iRow As Integer, iCol As Integer

 

[SkipVought]

I have run this code without error in Excel VBA.

Delete all characters before the DIM statement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Thank you, this cured the issue. Appreciate your assistance Skip, very much appreeciated. Can I ask why the spacing would have caused this to occur? After testing I put spacing back in as you had originally set up & worked fine?

 

[SkipVought]

I have this problem on an HP laptop. Somehow, the SPACE character is otherwise pasted???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[visible2you]

I appreciate and recommend answer given by "Skip".

You need to view this page.

http://www.ozgrid.com/VBA/ExcelRanges.htm