Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find "Last Empty Cell" Error Msg

0

Modified "Store_Referance_Last_Empty_Row" sub from website download.  I changed the input statement to Selection.PasteSpecial for pasteing values. Bottom line is it ran perfectly on downloaded sheet. I then created it in a new workbook. New workbook version generates "Run-Time Error '1004':  To do this, all merged cells need to be the same size.". Notes: Verified all columns (cells) are formatted the same and not part of any Merged area (however, Sheet does have merged cells elsewhere). Any suggestions work arounds to explore to correct this? Original file download "Find the Next Empty Row in Excel using VBA Macros" on TeachExcel.com

Sub NextTurn()

    ' Original from TeachExcel.com (Don)
    ' Store the cell reference for use latter in the macro
    ' Variable to hold the range reference
    ' Select the Next Empty Cell
    ' Select a Range of Cells for copying   - DAK
    ' Copy values in selected Cells         - DAK
    ' Paste values in the Next Empty Cells  - DAK
    ' Blanks Speed Selection Input          - DAK

Dim nextEmptyCell As Range          'Dimenisions varable "nextEmptyCell"

    Range("AN1:AS1").Select         'Selects data in cells AN1:AS1
    Selection.Copy                  'Copies data as selection

Set nextEmptyCell = Range("A" & Rows.Count).End(xlUp).Offset(1)

nextEmptyCell.Select                'Selects next empty cell
Worksheets("TurnControl").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False    'Pastes value into cells

    Range("W17:Y34").Select          'Selects Speed Selection Input
    Selection.ClearContents          'Blanks contents
    Range("AO2:AS3").Select          'Cursor rest

End Sub
Answer
Discuss

Discussion

Hi PepsiKid and welcome to the Forum

It's hard to see what's happening in that unstructured code text - please EDIT your original question and use the CODE button to add the macro (pasting the code where it says "Code_Goes_Here") and prefereably using the "Add files..." to attach an Excel file for us to check.

I'm not familar with Don's tuturial so it might help further if you use the LINK button to point us to its location.
John_Ru (rep: 6092) Sep 25, '21 at 12:11 pm
Sorry for 1st post, quite eveident I don't know structure of board, so used copy/paste because I know code would help. I agree it is much eaiser to read now. I don't understand about a link to tutorial (I found it searching Find Last Line on TeachExcel.com)
Pepsikid51 (rep: 2) Sep 25, '21 at 10:53 pm
Link?  find-the-next-blank-row-with-vba-macros-in-excel_1261.html 
Pepsikid51 (rep: 2) Sep 25, '21 at 11:04 pm
Thanks. You use the line from that in your code (and it works) but you're using it (I presume) on a sheet other than TurnControl. If you want to paste into the next blank row in the latter, you have to reference it (you don't have to activate it).   Incidentally you add a LINK using the button- like you did for CODE but if you copy the title on the page, use right click "paste as plain text" to get  this result: Find the Next Blank Row with VBA Macros in Excel   It's nearly always helpful to us if you attach a represntative Excel file to your question, using the Add files... button below the question text (when writing otr editting). It shouldn't include people's personal data and doesn't need to be a massive file- 10-30 representative data lines is often enough.
John_Ru (rep: 6092) Sep 26, '21 at 2:24 am
Thanks, I'll do much better in future. This is how I learn (task motivated), too old, too impatient, too stuborn for structured learning of things like VBA as a whole. I learn what I need... almost all of it I'll never use again if ever. I thank you for your assistance, will post the answer I found (troubleshooting) my question.
Pepsikid51 (rep: 2) Sep 26, '21 at 4:22 am
Add to Discussion

Answers

0
Selected Answer

Pepsikid

I'm assuming that you run this macro from a sheet other than TurnControl but want to paste data into the latter.

You don't have to activate sheets and select the target range for the pasting (but you do have to reference them- e.g. see my change in bold to the Set command below).

Given PasteSpecial is a Range method (and nextEmptyCell has been declared as a Range), you can append it to the defined range- see the bold line starting nextEmptyCell.PasteSpecial below).

Finally I've added a line at the end of the sub to clear the dashed copy lines- keep it there (or after any copy/paste actions) you'll get an error. I've commented out your other lines, having moved the .Activate line down) and leave it to you to change them to suit.

Sub NextTurn()

    ' Original from TeachExcel.com (Don)
    ' Store the cell reference for use latter in the macro
    ' Variable to hold the range reference
    ' Select the Next Empty Cell
    ' Select a Range of Cells for copying   - DAK
    ' Copy values in selected Cells         - DAK
    ' Paste values in the Next Empty Cells  - DAK
    ' Blanks Speed Selection Input          - DAK

Dim nextEmptyCell As Range          'Dimensions varable "nextEmptyCell"

Set nextEmptyCell = Worksheets("TurnControl").Range("A" & Rows.Count).End(xlUp).Offset(1)

Range("AN1:AS1").Copy         'Copies data from cells AN1:AS1

nextEmptyCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False    'Pastes value into cells


'Worksheets("TurnControl").Activate
    'Range("W17:Y34").Select          'Selects Speed Selection Input
    'Selection.ClearContents          'Blanks contents
    'Range("AO2:AS3").Select          'Cursor rest

Application.CutCopyMode = False ' cancel dashed lines around copied range

End Sub
You can find out more about that in the Tutorials section; these may be of interest to you Excel VBA Tip - Stop Selecting Cells!Copy one range and paste in another range.

Hope this helps.

Discuss
0

Error was caused because of missing character in "Set nextEmptyCell" line. Range should have been "AN" not "A" as in original example. Code was working properly all along, error was caused because Column A was a merged cell on "TurnControl" sheet.

Discuss

Discussion

Okay but please note that in general Answers should be solutions to user questions . This Answer kind of was but I hope my solution was informative anyway 

I have to say I wondered if your spreadsheet was really that big but your comment seemed to confirm the range was from AN1:AS1
John_Ru (rep: 6092) Sep 26, '21 at 9:11 am
Hi Pepsikid51,

John has provided a very good solution for you. Here are a couple of other options you may want to consider. After you determine where the next empty cell is you could use:
Range("AN1:AS1").Copy nextEmptyCell
 


or
nextEmptyCell.Value = Range(“AN1:AS1”).Value
 


Also, in your code, everything after "xlPasteValues" is not really needed.
And as John pointed out, adding the line "CutCopyMode = False" is important and needed. I would place it right after the paste operation.
Cheers.   :-)
WillieD24 (rep: 537) Sep 26, '21 at 9:53 am
Thanks Willie, nice tips. On "CutCopyMode = False", normally I would "place it right after the paste operation (while I remembered) but suggested  leaving it to the end, assuming other paste/copy opoerations might occur..
John_Ru (rep: 6092) Sep 26, '21 at 10:32 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login