Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
RJB RJB is offline
external usenet poster
 
Posts: 86
Default Macro to go to next blank cell in column

I've read what I could on this website, and in books, and I'm still a bit,
well, lost.

Every week, I will be dumping data into a spreadsheet. Every week, it will
be various lengths... One week 250 rows, the next 128, the next 372, etc.

I am doing some other stuff with Macros, but I need to get the cursor to go
to the FIRST blank cell in a column.

If I'm not running a Macro, I would just type "Ctrl" + Down Arrow to get to
the last FILLED ("Active"?) cell, and then would just Down-Arrow again to get
to the blank one directly below.

When I try to teach a Macro how to do this, it always remembers the SPECIFIC
cell I chose, so it will either go to the middle of nowhere, or start
overwriting in the table.

(In other words - the three lists I mentioned above. Cell A251 would be the
first blank in the first. What I want the Macro to do the following week is
go to A129, and the following week A373. The best I've been able to do is get
it to go to A251 EVERY FLIPPIN' TIME.)

Please help.

And please assume I am an absolute moron. If you just type something like
"DIM.CELL ="9999"
Plus.GoTo.ApplyDirectlyToTheForehead.ApplyDirectly ToTheForehead."
.... I will be totally dumbfounded.

Thanks!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to go to next blank cell in column

I assume you want to by-pass extraneous blanks and go to the first blank row
below all data.

Easiest is to go to bottom of column then come back up from there.

Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub

Or to set that cell as a range to copy to.........

Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)


Gord Dibben MS Excel MVP

On Tue, 27 Feb 2007 21:21:16 -0800, RJB wrote:

I've read what I could on this website, and in books, and I'm still a bit,
well, lost.

Every week, I will be dumping data into a spreadsheet. Every week, it will
be various lengths... One week 250 rows, the next 128, the next 372, etc.

I am doing some other stuff with Macros, but I need to get the cursor to go
to the FIRST blank cell in a column.

If I'm not running a Macro, I would just type "Ctrl" + Down Arrow to get to
the last FILLED ("Active"?) cell, and then would just Down-Arrow again to get
to the blank one directly below.

When I try to teach a Macro how to do this, it always remembers the SPECIFIC
cell I chose, so it will either go to the middle of nowhere, or start
overwriting in the table.

(In other words - the three lists I mentioned above. Cell A251 would be the
first blank in the first. What I want the Macro to do the following week is
go to A129, and the following week A373. The best I've been able to do is get
it to go to A251 EVERY FLIPPIN' TIME.)

Please help.

And please assume I am an absolute moron. If you just type something like
"DIM.CELL ="9999"
Plus.GoTo.ApplyDirectlyToTheForehead.ApplyDirectl yToTheForehead."
... I will be totally dumbfounded.

Thanks!


  #3   Report Post  
Posted to microsoft.public.excel.misc
RJB RJB is offline
external usenet poster
 
Posts: 86
Default Macro to go to next blank cell in column

That first one worked perfectly.

If you have a moment, explain what each command is doing?

Thank you

"Gord Dibben" wrote:

I assume you want to by-pass extraneous blanks and go to the first blank row
below all data.

Easiest is to go to bottom of column then come back up from there.

Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub

Or to set that cell as a range to copy to.........

Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)


Gord Dibben MS Excel MVP

On Tue, 27 Feb 2007 21:21:16 -0800, RJB wrote:

I've read what I could on this website, and in books, and I'm still a bit,
well, lost.

Every week, I will be dumping data into a spreadsheet. Every week, it will
be various lengths... One week 250 rows, the next 128, the next 372, etc.

I am doing some other stuff with Macros, but I need to get the cursor to go
to the FIRST blank cell in a column.

If I'm not running a Macro, I would just type "Ctrl" + Down Arrow to get to
the last FILLED ("Active"?) cell, and then would just Down-Arrow again to get
to the blank one directly below.

When I try to teach a Macro how to do this, it always remembers the SPECIFIC
cell I chose, so it will either go to the middle of nowhere, or start
overwriting in the table.

(In other words - the three lists I mentioned above. Cell A251 would be the
first blank in the first. What I want the Macro to do the following week is
go to A129, and the following week A373. The best I've been able to do is get
it to go to A251 EVERY FLIPPIN' TIME.)

Please help.

And please assume I am an absolute moron. If you just type something like
"DIM.CELL ="9999"
Plus.GoTo.ApplyDirectlyToTheForehead.ApplyDirectl yToTheForehead."
... I will be totally dumbfounded.

Thanks!



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to go to next blank cell in column

ActiveSheet.Cells(Rows.Count, 1) finds the bottom of Column 1(A) which is
A66536

Sub findbottom2()
Dim rng As Range
Set rng = ActiveSheet.Cells(Rows.Count, 1)
MsgBox rng.Address
End Sub

Then the End(xlUp) comes back up to last used cell in Column 1

Sub findbottom3()
Dim rng As Range
Set rng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
MsgBox rng.Address
End Sub

The Offset(1, 0) selects the cell below last filled cell.

Sub findbottom4()
Dim rng As Range
Set rng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
..Offset(1, 0)
MsgBox rng.Address
End Sub


Gord

On Tue, 27 Feb 2007 22:05:05 -0800, RJB wrote:

That first one worked perfectly.

If you have a moment, explain what each command is doing?

Thank you

"Gord Dibben" wrote:

I assume you want to by-pass extraneous blanks and go to the first blank row
below all data.

Easiest is to go to bottom of column then come back up from there.

Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub

Or to set that cell as a range to copy to.........

Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)


Gord Dibben MS Excel MVP

On Tue, 27 Feb 2007 21:21:16 -0800, RJB wrote:

I've read what I could on this website, and in books, and I'm still a bit,
well, lost.

Every week, I will be dumping data into a spreadsheet. Every week, it will
be various lengths... One week 250 rows, the next 128, the next 372, etc.

I am doing some other stuff with Macros, but I need to get the cursor to go
to the FIRST blank cell in a column.

If I'm not running a Macro, I would just type "Ctrl" + Down Arrow to get to
the last FILLED ("Active"?) cell, and then would just Down-Arrow again to get
to the blank one directly below.

When I try to teach a Macro how to do this, it always remembers the SPECIFIC
cell I chose, so it will either go to the middle of nowhere, or start
overwriting in the table.

(In other words - the three lists I mentioned above. Cell A251 would be the
first blank in the first. What I want the Macro to do the following week is
go to A129, and the following week A373. The best I've been able to do is get
it to go to A251 EVERY FLIPPIN' TIME.)

Please help.

And please assume I am an absolute moron. If you just type something like
"DIM.CELL ="9999"
Plus.GoTo.ApplyDirectlyToTheForehead.ApplyDirectl yToTheForehead."
... I will be totally dumbfounded.

Thanks!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Last non-blank cell in a column Syd Excel Discussion (Misc queries) 2 December 9th 06 02:48 AM
Ending a macro early conditionally on one cell being blank Rokuro kubi Excel Discussion (Misc queries) 3 May 26th 06 02:09 PM
sum using or (if Blank cell in Column) Dreadhead New Users to Excel 1 April 4th 06 07:00 PM
macro to hide rows if cell is blank Shooter Excel Worksheet Functions 3 September 28th 05 10:55 PM
Value of last non-blank cell in a column KemS Excel Worksheet Functions 2 March 17th 05 11:41 PM


All times are GMT +1. The time now is 09:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"