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]

Thanks, rsxchin. That worked great.

You must be superior intelligence to crank out the code this way. This doesn't strike me as a simple language with all the possibilities there are...

Well, if you are up to it. I would love to get the one button to work for both areas. I've been working to simplify the data entry for the sheet. It's definitely a work in progress. A little streamlining sure makes a big difference, though...

EDIT

rsxchin...don't know where to start, but I think I would like to get the button to bounce back and forth from the first empty cells of the ranges. This seems extraordinarily complicated to me, but if you get an opportunity, I would be ever so grateful. I can see that I am going to be sucked into this language somehow someday...

 

[mirabeau]

Hi XLXLXL,

Just had a look at the title of your post, but not the details of the thread.

Possibly useful information for you, possibly not, but if you want to find the first empty cell in a column (say Col A) then the following code is quick, short and easy

Sub firstempty()
Range("A:A").SpecialCells(4)(1).Select
End Sub

 

[XLXLXL]

mirabeau...thanks for the reply. Do you know of an easy way to set up a macro so that a button is set to go to the first empty cell of a range in a column, then to the first empty cell of another range in the same column, then back to the first one and so on (back and forth, basically)?

This seems like it would be a very handy macro to keep around idk...

Thanks again...

 

[Glory]

Use a flag. Each time the button is pressed, "Toggle" = either 1 or 0. If Toggle = 1 then, if Toggle = 0 then. Follow each condition with the code that points to the desired range.

 

[Glory]

If Toggle = 1 then

'code with range1

Toggle = 0

Exit Sub

ElseIf Toggle = 0 Then

'code with range2

Toggle = 1

Exit Sub

 

[Glory]

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

I figured this person would want to be able to find the first blank cell so they could locate the end of the used range in a column. Maybe I figured wrong.

 

[XLXLXL]

Hey, Glory

No luck with the code...

Here is what I had before:

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

This gets me to the first empty box in the first range (J17:J1240). Here is what I tried using your advice. I got an invalid outside process or something or another message:

If Toggle = 1 then
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
Toggle = 0
Exit Sub
ElseIf Toggle = 0 Then
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 = 1261 To 2484
If Cells(i, "J") = "" Then
Cells(i, "J").Select
Exit Sub
End If
Next i
End Sub
Toggle = 1
Exit Sub

I didn't realize macros could be fun. I'm not planning on making a living at it, though...

Please let me know if you have any ideas...

Thanks again

 

[Glory]

Put "If Toggle = 1" down below the statement "Sub" and see what it does next.

 

[XLXLXL]

Glory...no luck , but is this what you meant?

Sub last_row()
If Toggle = 1 then
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
Toggle = 0
Exit Sub
ElseIf Toggle = 0 Then
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 = 1261 To 2484
If Cells(i, "J") = "" Then
Cells(i, "J").Select
Exit Sub
End If
Next i
End Sub
Toggle = 1
Exit Sub

...

 

[Glory]

I can see a couple of problems. Here, the toggler is all on its own outside the procedure. Everything after "end sub" is going to get ignored by the VBA.

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
[COLOR=red]End Sub[/COLOR]
Toggle = 0
Exit Sub
ElseIf Toggle = 0 Then

All your code has to come inside of procedures. You can do other things outside procedures, and in fact it might be necessary to do that here.

You need to use one procedure for your toggler. One procedure that is activated by the command button, that calls the other procedures based on what the toggler says to go. You may need to declare toggle as a public static variable.

Public Static Toggle as Boolean
 
Private sub part1()
 
If toggle = False then
 
   call top_part_last_row
 
elseif toggle = True then
 
   call bottom_part_last_row
 
end if
 
End sub

Private sub top_part_last_row()
 
for i = 17 to 1240
 
   If cells(i,"J") = ""
      cells(i, "J").Select
      Exit For
   End If
 
Next
 
Toggle = True
 
end sub

Private sub bottom_part_last_row()
 
for i = 1261 to 2484
 
   If cells(i,"J") = ""
      cells(i, "J").Select
      Exit For
   End If
 
Next
 
Toggle = False
 
end sub

Messed up part of it. Should be okay now?

There's also the way tusharm just nudged me toward, which we might look into here too if this method doesn't work.