Go To First Empty Cell Help

[XLXLXL]

Sorry, I am a novice at this. I would like to set up a cell so that when I click on the cell, I go to the first empty cell in a column range in Excel. Is this possible? If so, what do I need to enter into the cell? Thankyou in advance for any help...

 

[XLXLXL]

OK, I entered all the code as you suggested and got a "Expected: Sub or Function or Property" error. Here's what I used:

Public Static Toggle as Boolean

Sub part1()

If Toggle = False Then

Call top_part_last_row

ElseIf Toggle = True Then

Call bottom_part_last_row

End If

Toggle = True

End subCode:
Sub top_part_last_row()

For i = 17 To 1240

If cells(i,j) = ""
Cells(i, "J").Select
Exit For
End If

Toggle = 1

Next

end subCode:
Sub bottom_part_last_row()

For i = 1261 To 12484

If cells(i,j) = ""
Cells(i, "J").Select
Exit For
End If

Next

Toggle = False

End Sub

The ones in red showed up that way when I attempted to close the window...

Ha ah...beaten to oblivion by a toggler...

 

[Glory]

 Public Toggle
 
Private Static Sub CommandButton1_Click()
MsgBox Toggle
If IsEmpty(Toggle) Or Toggle = False Then
    Call Top_Part
    Exit Sub
End If
If Toggle = True Then: Call Bottom_Part
End Sub

Private Sub Top_Part()
Dim aim As Object, theRange As Range, AddressArray As Variant
Set aim = Application.Intersect(Range("J17:J1240"), UsedRange)
If aim Is Nothing Then
   MsgBox "There's no contents in column J, Range(""17:J1240"")"
   Exit Sub
End If
AddressArray = Split(aim.Address, "$")
Address1 = AddressArray(4)
Range("J" & Address1).Offset(1, 0).Activate
Toggle = True
End Sub

Private Sub Bottom_Part()
Dim aim As Object, theRange As Range, AddressArray As Variant
Set aim = Application.Intersect(Range("J1261:J2484"), UsedRange)
If aim Is Nothing Then
   MsgBox "There's no contents in column J, Range(""J1261:J2484"")"
   Exit Sub
End If
AddressArray = Split(aim.Address, "$")
Address1 = AddressArray(4)
Range("J" & Address1).Offset(1, 0).Activate
Toggle = False
End Sub

I tested this, and it works, but it has one serious drawback: it uses the UsedRange of your sheet, which means that once you type something in past a certain range, you either have to delete the rows, save and reopen, or else they will continue to constitute part of the usedrange.

That means your "first" empty cell may be thousands of rows below your current empty cell, because this is targeting the first cell past the usedrange in each of the ranges you specified.

Give me another few minutes.

 

[Glory]

Public Toggle
 
Private Static Sub CommandButton1_Click()
 
If IsEmpty(Toggle) Or Toggle = False Then
 
    Call Top_Part
    Exit Sub
 
End If
 
If Toggle = True Then: Call Bottom_Part
 
End Sub

Private Sub Top_Part()
 
For i = 17 To 1240
 
    If Cells(i, "J") = "" Then
 
        Cells(i, 10).Select
        Exit For
 
    End If
 
Next
 
Toggle = True
 
End Sub

Private Sub Bottom_Part()
 
For i = 1261 To 2484
 
    If Cells(i, 10) = "" Then
 
        Cells(i, 10).Select
        Exit For
 
    End If
 
Next
 
Toggle = False
 
End Sub

 

[XLXLXL]

Thanks so much, Glory, for helping so much.

Just a little info on the spreadsheet...

This spreadsheet has fixed ranges. In the two sections where the button seeks the empty cells, there is no other data entered other than on those rows. Actually, there is only a miniscule amount of data added to the top to identify the week of the year and a reference number. These numbers are used for some calculations at the top of the spreadsheet, along with the totals from the two data sections below. It's a comparison thing. Also, the entire sheet is protected with the exception of the data entry areas. The groups of numbers are totalled separately for the two data sections and then various comparisons made up top. It's a very "fixed" spreadsheet. It won't ever need more rows, thankfully...

 

[Glory]

You bet.

IMHO this stuff stays fun exactly as long as there's others around to give your brain a swift kick into gear. This place is great for that.

 

[XLXLXL]

Glory...I copied and pasted the code but no luck. I got a message about the macro possibly being unavailable or that macros might be disabled. The macros are enabled for all spreadsheets, so I know that's not the problem. Not sure what would cause a macro to be unavailable to a particular worksheet...

All you guys are a huge help here. I really appreciate the effort you are making to help me improve this spreadsheet. I am really confident that I'll be able to get it working because of the help...

Thanks

 

[XLXLXL]

Well, I am going to sleep. Thanks again for the help Glory. I am confident I'll get it figured out. For now the button works for the first section of the data, and that's a big improvement from before...

 

[mirabeau]

mirabeau, it looks like that method only works if a part of the currently used range is blank. Am I right about that?

Pretty much right. In the case you point out that one-liner won't work on the first run of the code, which does rather diminish its attractiveness.

But could use a two-liner instead, needed on the first run only. Something like

Range("A:A")(Rows.Count).Font.Bold = True
Range("A:A").SpecialCells(4)(1).Select

Or other possibilities without looping though cells.

 

[RSXchin]

Here you go. It works for both now. copy the ENTIRE thing, and overwrite the ENTIRE module.

Public Toggle As Boolean
Private Sub Top_Part()
    If Toggle = False Then
        For i = 17 To 1240
            If Cells(i, "J") = "" Then
                Cells(i, 10).Select
                Exit For
         
            End If
        Next
        Toggle = True
        Exit Sub
    Else
        For i = 1261 To 2484
            If Cells(i, 10) = "" Then
                Cells(i, 10).Select
                Exit For
            End If
        Next
    Toggle = False
    End If
End Sub

 

[XLXLXL]

OK...

rsxchin...I tried this one, but I got a message

"Can not run the macro...The macro may not be available in the workbook or all macros may be disabled."

I just put it back to where it was before after each time, so the button will work for the first series. Of course, I can always resort to the 2nd button, so...