Go to - special

[user21136]

Hi,

Using the "Go To" under "Edit", how do you find the first blank cell following numbers in a column please. I'm trying to record my first macro.

ie

Column C
12
34
56
68
<----This cell

Cheers Glenn

 

[Todd Bardoni]

You can still use the macro recorder. Start the recorder, hit the Relative Reference button, select cell C65536, hold down the control key and hit the up arrow key and then let go of the control key and hit the down arrow key. Poof, you have your first cell below your last row of data.

Thanks Richie and Nate for the lesson.

 

[user21136]

Hi Smitty,

> Best advice is to record away and when you've got a question post back!

OK, I select:

1. Edit
2. Go To, insert the range which is C7:C20 (note that C7 down to C15 is filled with numbers so I want to find cell C16)
3. Go To - Special

...and I'm stuck there! I've tried numerous things but can't get that cell.

Cheers Glenn

 

[Todd Bardoni]

Glen, no disrespect, but forget about using Go To...its not gonna do it for ya. See my post above.

 

[NateO]

A quick translation for those who don't speak NateO :wink:

Sub NateO()

MsgBox [c65536].End(3).Item(2).Address

'End can be one of 4 directions; Left,Right,Up,Down : So 3=Up

'Item is used to offset from the specified range, uses rows if only one argument
' Row 1 is the row of the range itself so 2 is down 1 row

End Sub

PS. Nate - couldn't get Item to work without actually adding "Item" - is this because End was used first?

This is a good translation! You can't get the following to fire:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> test()
MsgBox [c65536].End(3)(2).Address(0, 0)
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

?

2, is shorthand for item, it works similarily to a collection if you will. Ranges can be fun, work as arrays and collections...

3 is not a column reference, c65536 serves as that. 3 is an acceptable, although not the official, constant for xlup. If you go into the object browser and have a look at xlup, you'll notice:

Const xlUp = -4162 (&HFFFFEFBE)

You could use -4162 in place of xlup. In Excel you could simply use xlup, and probably should for transparency.

I in my board and non-board life I end up doing so much interfacing of applications, Lotus Notes, Access, Word, and one coming to board near you (that doesn't work for me!) and I use late-bound code where I can't use xl constants (anything: xl_______). So I got to playing with the end property and found 3 is good to go. Since xlUp is so prevelent and 3 is shorter, I remember 3 works and I use it in Excel, Access, wherever I go...

Perhaps not the best advice, but the method to my madness. :wink:

 

[user21136]

tbardoni,

Your too quick for me! Thanks.

> no disrespect

Definately none taken

Thanks again, Glenn.

 

[user21136]

Hi,

I have recorded this macro:

---------------------8<--------------------
Sub CopyToTradelog()
Range("C36").Select
Selection.Copy
Sheets("TradeLog").Select
Application.Goto Reference:="R106C3"
Selection.End(xlUp).Select
Range("B12").Select
ActiveSheet.Paste
End Sub
---------------------8<--------------------

...which I'm guessing could be shortened but I'm not too fussy with it as long as it works!

One other thing, could you show me how to repeat the same thing and find the next cell down everytime I run the macro.

For example I enter a new number in C36, I would like that new number placed below the last one into Range("B13").Select (using the above code).

Cheers Glenn

 

[Todd Bardoni]

Sub CopyToTradelog() 
     Range("C36").Copy 
     Sheets("TradeLog").Range("B65536").End(xlUp).PasteSpecial 
End Sub

 

[NateO]

Hello again, same issues, selecting, last cells... Past special can be good, especially for manual cutting, but it selects cells! Bad...

How about:

sub sdfhsdfjkhsd()
[b65536].end(3)(2) = [C36]
end sub

 

[Todd Bardoni]

Hello again, same issues, selecting, last cells... Past special can be good, especially for manual cutting, but it selects cells! Bad...

How about:

sub sdfhsdfjkhsd()
[b65536].end(3)(2) = [C36]
end sub

Re: Paste Special:-

That never ocurred to me!

Nate, once again, bless you for the lesson. This is a huge revelation for me!

 

[NateO]

Hello T,

Re: Paste Special:-

That never ocurred to me!

Nate, once again, bless you for the lesson. This is a huge revelation for me!

You are welcome. This is really bad behaviour in my estimation, it can really bog down a procedure. I hope MS fixes this one of these years! It really detracts from what is otherwise very nice native functionality.

Have a good one.