Return Cell Address with VBA

another rachel

Board Regular
Joined
May 27, 2002
Messages
80
Hello
I am trying to see if the macro I have started to write is working.
However, forget how to use the msgbax command....

I have written the following and would like the macro to return the cell reference of the first empty cell.

I know this is easy as I have done it before but can't remember how :oops:

Any assistance, as akways, is much appreciated
Regads
Rachel
Code:
Dim lastnumber As Range
Dim firstemptycell As Range

Set lastnumber = Range("A65536").End(xlUp)
Set firstemptycell = lastnumber.Offset(rowoffset:=1, columnoffset:=0)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
You can probably eliminate the "firstemptycell" reference as it seems unnecessary:
Code:
    Dim lastnumber As Range
        
        Set lastnumber = Range("A65536").End(xlUp)
        MsgBox lastnumber.Offset(1).Address
For a Message Box refresher, just enter: "MsgBox Function" in the VBA helpfile.

Hope that helps,

Smitty
 
Upvote 0
using last cell refrence

Thank you!

In a very annoying move, the helpfile in VBA Excel has been "switched off" at my place of work. :x

The reason I think I need to define the first empty cell is because the macro needs to copy data and paste into the first empty cell as follows:
Code:
Dim lastcell As Range
Dim firstemptycell As Range

Set lastcell = Range("A65536").End(xlUp)
Set firstemptycell = lastcell.Offset(1)
    
    Range("A3:D14").Copy
    Range("firstemptycell").Select
    ActiveSheet.Paste

However now the selectin of firstemptycell is not working
Any suggestions?
Rachel
 
Upvote 0
You might try to kill a couple birds with one stone.

I modified your code a bit to this:

Code:
Dim lastcell As Range
Dim firstemptycell As Range

Set lastcell = Range("A65536").End(xlUp)
Set firstemptycell = lastcell.Offset(1)
    
    Range("A3:D14").Copy firstemptycell

First, the range firstemptycell (and lastcell, as well) are range variables, not named ranges, so you wouldn't enclose them in quotes. Also, you can specify the destination range for the copy by just adding a space, then the destination on the same line (as long as you're only doing a Paste, not a PasteSpecial).

One more thing: Instead of using Range("A65536"), you might try using Range("A" & Rows.Count), as (eventually) a version of Excel you'll be using will have more than 65,536 rows, and you won't want to go through a bunch of code, correcting it.

Hope that helps!

And go slap the IT guy who turned off the VBA Help (what a stoopid move!). :x

(Heya Smitty!)
 
Upvote 0
Thank you!
The piece of code is a thing of beauty!

Am taking a break from my spreadsheet for a few hours but no doubt will be back with questions as I attempt my first loop. :biggrin:
 
Upvote 0
Tazguy37
In the mean time...as you mentioned, I also need to complete a paste special....

The following code does not work, I have tried removing the quotes but I presume that I am still missing a basic step!
Code:
Range("A3:D14").Copy
Range(fistemptycell).PasteSpecial Paste:=xlPasteValues

Thank you
Rachel
 
Upvote 0
Like Todd mentioned, firstemptycell is a variable, so in addition to not eclosing it in quotes, you refer to it directly. Here are two different examples of codd that does the same thing, based on what you posted so far:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> lastcell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> firstemptycell <SPAN style="color:#00007F">As</SPAN> Range
    
        <SPAN style="color:#00007F">Set</SPAN> lastcell = Range("A65536").End(xlUp)
        <SPAN style="color:#00007F">Set</SPAN> firstemptycell = lastcell.Offset(1)
      
        Range("A3:D14").Copy
        firstemptycell.PasteSpecial Paste:=xlPasteValues
        
        Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> foo2()
    <SPAN style="color:#00007F">Dim</SPAN> lastcell <SPAN style="color:#00007F">As</SPAN> Range
    
        <SPAN style="color:#00007F">Set</SPAN> lastcell = Range("A65536").End(xlUp)
      
        Range("A3:D14").Copy
        lastcell.Offset(1).PasteSpecial Paste:=xlPasteValues
    
        Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

('Sup Todd! :))
 
Upvote 0
another rachel said:
Tazguy37
In the mean time...as you mentioned, I also need to complete a paste special....

The following code does not work, I have tried removing the quotes but I presume that I am still missing a basic step!
Code:
Range(fistemptycell).PasteSpecial Paste:=xlPasteValues

Thank you
Rachel

Yes, you are missing something. Don't get discouraged, because it can be a tough thing to grasp (I still have troubles with this, myself).

firstemptycell (you did spell it incorrectly in your last post) is a range object (it's Dim'd "as Range"), so when you try to do this:

Code:
Range(firstemptycell).PasteSpecial Paste:=xlPasteValues

you're trying to make firstemptycell a range (which it already is). Now, if firstemptycell had been a named range, you'd put it in quotes, and enclose it in parenthesis using Range(). You'd do it then because "firstemptycell" is just a string that references a range.

If you had instead tried:

Code:
Dim lastcell As Range
Dim firstemptycell As Range

Set lastcell = Range("A65536").End(xlUp)
Set firstemptycell = lastcell.Offset(1)
    
    Range("A3:D14").Copy
    firstemptycell.PasteSpecial Paste:=xlPasteValues

now that would work. Do you see why?

Please post back if you have any more questions on this. Like I said, it can be a difficult concept to grasp.
 
Upvote 0
Thank you - I think that I undestand the use of quotes in cell references now.

Next issue - I have another 20 columns of data that I need to copy and paste so that they are not across the page but down the page - I am sure that I can use a loop - though not sure how to tell the loop to complete for 20 columns.

Second issue, I was presuming that each time the code came to the "firstemptycell" command it would recalculate and add the newest data to the bottom of column A. The script that I have results in continual re-writing over the first empty cell that was identified.
Code:
Dim lastcellcolumnA As Range
Dim firstemptycellcolumnA As Range
 
Upvote 0
Apologies
Stuffed up entering the sample code :oops:
Code:
Dim lastcellcolumnA As Range
Dim firstemptycellcolumnA As Range
Set lastcellcolumnA = Range("A65536").End(xlUp)
Set firstemptycellcolumnA = lastcellcolumnA.Offset(rowoffset:=1, columnoffset:=0)

Range("L24").Copy firstemptycellcolumnA
Range("L25").Copy firstemptycellcolumnA
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top