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...

 

[Glory]

This would be really slow but it should work.

sub find_empty_cell()

For each cell in activesheet.Columns(X)
If Cell.Value = "" Then
Cell.Activate
Exit For
End If
Next

End Sub

 

[RSXchin]

try this one.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    For i = Selection.Row To Cells(Rows.Count, Selection.Column).End(xlUp).Row
        If Cells(i, Selection.Column) = "" Then
            Cells(i, Selection.Column).Select
            Application.EnableEvents = True
            Exit Sub
        End If
    Next i
    Application.EnableEvents = True
End Sub

 

[XLXLXL]

Wow...thanks for the quick replies.

OK, I don't enter this into the cell, right? Is this the logic for a macro? Sorry, but I haven't ever written one.

Any chance you could post a 1, 2, 3 procedure for turning the cell into a button with this function? The cell is D2, and the range is J:17 to J1240.

The spreadsheet is just one page and very large but very simple...

Thanks again for helping

 

[RSXchin]

Are you always looking in the same column?

 

[XLXLXL]

Yes, I add data throughout the year, so I just want one button at the top to take me to the first empty cell in the range (always the one column...range J17:J1240)...

 

[RSXchin]

goto the "Developer" tab

click on "insert"

click on the top left button in the dropdown

draw your button

A menu will come up asking about which macro to use

click on new

and then post this code in the big empty white space

Sub last_row()
    LastRow = Cells(Rows.Count, "J").End(xlUp).Row
    Cells(LastRow, "J").Select
End Sub

 

[XLXLXL]

Got it to work, rsxchin, but there is one complication. The body of the spreadsheet is in two halves with comparative data at the top. I have the sheet frozen, so that most of what is seen is the comparative area with about 10 rows of the data entry area showing at the bottom. The button works but for some reason, it doesn't stop at the first empty cell in the top half, which is what I would like. This range ends with the cell J1240. The first cell is J17 in the range. Somehow, I am ending up at the cell above the first empty cell of the bottom half. The range for this area is J1261:J2484.

I was going to use whatever formula worked for the first range for the range for a second button for the bottom half, that's why I didn't mention the complication...

Any way to activate the button for the J17:J1240 range only?

I see how to edit the macro, so no need for explanations there, just add the correct code to the same box you mentioned using "Assign Macro"...

Really appreciate the help you are providing. I am actually starting to get a little fired up about Excel! I didn't even know there was a "Developer's Tab". I had to run to "Help" to find it...it was hidden...

 

[RSXchin]

Make a second button and put the 2nd sub in it. Also, I changed the first code I gave you so it'll select the correct row.

Sub last_row()
    LastRow = Cells(Rows.Count, "J").End(xlUp).Row
    Cells(LastRow + 1, "J").Select
End Sub

Sub Top_Part_last_row()
    For i = 17 To 1240
        If Cells(i, "J") = "" Then
            Cells(i, "J").Select
            Exit Sub
        End If
    Next i
End Sub

 

[XLXLXL]

rsxchin, sorry my last post is confusing...there are 3 areas...2 data entry areas (the ranges listed) and the comparative area at the top. There are a few empty cells between the comparative area and the areas below. Because of that and because there are the two data entry areas, I was hoping to get a button activated for each of the areas using the ranges...

Actually, now that I think about it, the best for the button would be code that went to the first open cell in the first range of the data area with the first click and then to the first open cell in the range of the second area with the second click. Then I could just use the same button for both areas. I enter data for the two areas in the same session each time for the most part...

Thanks, I will give the code a try...