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]

The code works as posted, but you have to link it to the button.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Push “Shift+F11”. ON the left, you should see the “Project Explorer”… it’s a toolbar that lets you view different projects. If not, press “Ctrl+R”.
<o> </o>
Double-click on the worksheet where you’re going to place your command button. Paste the code in exactly as it appear in my previous post, or the way it appears below (edited based on rsxchin’s suggestion, which is a good one).
<o> </o>
For good measure, go right-click on your command button in the worksheet, select “Properties”, and make sure the name of the command button is “commandbutton1”.
<o> </o>
Or rename it however you want, just be sure that the first sub in the code I posted is named the same as the command button… and that the code is pasted into the worksheet module for the sheet you’re using.
<o> </o>

Public Toggle
<o:p> </o:p>
Private Static Sub CommandButton1_Click()
<o:p> </o:p>
If Toggle = False Then
<o:p> </o:p>
For i = 17 To 1240
<o:p> </o:p>
    If Cells(i, "J") = "" Then
<o:p> </o:p>
        Cells(i, 10).Select
        Exit For
<o:p> </o:p>
    End If
<o:p> </o:p>
Next
<o:p> </o:p>
Toggle = True
<o:p> </o:p>
Else
<o:p> </o:p>
For i = 1261 To 2484
<o:p> </o:p>
    If Cells(i, 10) = "" Then
<o:p> </o:p>
        Cells(i, 10).Select
        Exit For
<o:p> </o:p>
    End If
<o:p> </o:p>
Next
<o:p> </o:p>
Toggle = False
<o:p> </o:p>
End Sub

<o> </o>
It works great. Just tested it again to be absolutely sure. Lemme know how it goes for you.
<o> </o>
<o></o>
<o></o>
@ mirabeau: I don’t understand your suggestion, but I’d like to. Anything that would avoid iterating through the cells, because I have something similar that I’m trying to figure out. I’m gonna PM you just to be sure the question finds you again, hope you don’t mind.
<o> </o>

 

[RSXchin]

Hooray!!! My code worked!!!!

 

[XLXLXL]

Glory, I am a total noob. I am sorry, but I cannot get the tools you mentioned up. Shift + F11 brings up a new sheet tab and CTRL + R duplicates the cell left of the selected cell. One thing I noticed. When I right click on the button, I don't get "Properties". There is an "Assign Macro" choice and a few others like "Cut", "Copy", and "Paste" and "Edit Text". No choice for "Properties". "Format", "Grouping", and "Order" are the others, if you are curious to know.

I did get the 2 button setup going, which I like.

I wish I understood the process better...not the programming logic. That is a hopeless battle for me, right now. I just don't understand the procedures for using macros well enough to be able to follow your instructions, sometimes.

Thanks to you guys for making my life alot easier. The 2 button setup is great for this spreadsheet. There are only two areas for data entry in the sheet, so it's not like I need alot of buttons. Also, one button could not be moved from it's location, but the addition of the second button gave me the opportunity to put the button I use more during use, closer to where I actually work in the spreadsheet.

Thanks again!

 

[RSXchin]

click on the Developer tab
Go to Designmode
double click on your button
copy and paste that code into the module for it (erase all the rest of it first)

This code makes the SINGLE button functional for the top, then the bottom, then the top again, and so on.

 

[Glory]

"alt+F11" will open up the VBA editor.

Also, try this: press "alt+F8", then "Options", and assign an accelerator to your macro. A shortcut command; try clicking in the textbox, holding shift and pressing "A". To accomplish this, you need to remove the "Private" before the "Sub" and replace it with "Public".

Public Toggle
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Public Static hopper()
<o:p> </o:p>
If Toggle = False Then
<o:p> </o:p>
For i = 17 To 1240
<o:p> </o:p>
    If Cells(i, "J") = "" Then
<o:p> </o:p>
        Cells(i, 10).Select
        Exit For
<o:p> </o:p>
    End If
<o:p> </o:p>
Next
<o:p> </o:p>
Toggle = True
<o:p> </o:p>
Else
<o:p> </o:p>
For i = 1261 To 2484
<o:p> </o:p>
    If Cells(i, 10) = "" Then
<o:p> </o:p>
        Cells(i, 10).Select
        Exit For
<o:p> </o:p>
    End If
<o:p> </o:p>
<o:p>End If</o:p>

Next

That way you don't have to push any buttons at all. If you're the only person using the macro, you might find this a much more efficient solution.

Whenever you want to hop up or down, push "ctrl+shift+A".

There's also another solution if you want to be able to hop around real easily...

You can set up the macro to delete and recreate the second button based on the location of the cursor, or the last used row on the spreadsheet. But you'd have to be willing to rename the button, and it sounds like you're reluctant to walk down that way.

 

[Glory]

Public Toggle
 
Private Static Sub CommandButton1_Click()
For Each Shape In activesheet.Shapes
    Shape.Delete
Next
 
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
 
Else
 
    For i = 1261 To 2484
 
        If Cells(i, 10) = "" Then
            Cells(i, 10).Select
        Exit For
        End If
 
 Toggle = False
 
    Next
 
End If
 
With activesheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
            DisplayAsIcon:=False, left:=120, top:=72, width:=50.4, height:=30)
            .name = "CommandButton1"
            .Object.Caption = "Bottom"
End With
 
With activesheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
            DisplayAsIcon:=False, left:=520, top:=(ActiveCell.top), width:=50.4, height:=30)
            .name = "CommandButton2"
            .Object.Caption = "Top"
End With
 
End Sub
 
 
 
Private Sub CommandButton2_Click()
 
Cells(1, 1).Select
 
End Sub

I got curious about what that code might look like, and here's a working example.

You need to create two buttons right off the bat. Doesn't matter where you put them for now.

Right click both of them, and click properties one by one. Make sure they are named "CommandButton1" and "CommandButton2".

When that's through, push "Alt+F11".

Push "Alt+r" if you can't see the project explorer on the left side of the screen.

Double-click on the sheet that holds your two new command buttons.

Paste the two procedures above into that blank worksheet module.

Then close the VBA editor and go click your buttons.

You may have to edit "Left" based on the width of the columns in your worksheet. We can help with that.

Also, somebody may have a way to delete just one button at a time, instead of all the shapes on the sheet the way my version works now. I don't know how to do that. But it's working, anyway, and that's something.

What the code does is place a second button right next to wherever your cursor goes when you push the first button. Both buttons are deleted and recreated each time you run the code, so there's no worry of buttons stacking up on you.

Try it out, see how it works.

 

[Glory]

Revised code doesn't delete either button... can't explain why I didn't think to just move the second button in the first place. Chalk it up to sleep deprivation.

Still can't know exactly where you want the second button with regard to the "Left" property.

Remember to follow the directions in post 35 before you paste this code into the worksheet module, if you want to try this out.

Public Toggle
Public Sub CommandButton1_Click()
'Checks for the first empty cell
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
Else
    For i = 1261 To 2484
        If Cells(i, 10) = "" Then
            Cells(i, 10).Select
        Exit For
        End If
Toggle = False
    Next
End If

'Moves the second command button beside the first blank cell
With ActiveSheet.OLEObjects("CommandButton2")
    .Left = ActiveCell.Left + 70
    .Top = ActiveCell.Top
End With

End Sub
Private Sub CommandButton2_Click()
Cells(1, 1).Select
End Sub

 

[XLXLXL]

Thanks you guys for all the help. I have got the two buttons going, which is actually very efficient. The spreadsheet opens in the top data section, anyway, so all I have to do is click on the second button (which I have positioned closer to the data) once to go to the lower section. When I am done, I just click the original button that I was considering using for both, and I am back at the top section and ready to close the sheet.

I have thought it through, and I like the two button system better, so I am going to stick with it, but the code for the one button toggle is very cool and something I will keep around for use in the future.

Again, a special thankyou to all of you for your kind devotion to the especially needy of us out here...

 

[Hello World]

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

Thanks RSXchin, I also benefit from your help with these 2 lines of code.

thanks again.