Solved

I need a macro that will "go to next blank line" in Excel 2013

Posted on 2014-09-05
6
164 Views
Last Modified: 2014-09-05
I need a macro that will "go to next blank line" in Excel 2013. I can write simple macros, but this has me stumped. Any ideas?
0
Comment
Question by:LLMadden
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40306590
Typically from any selected cell, to move to and select the next blank line:
 
ActiveCell.End(xlDown).Offset(1, 0).Select

Open in new window


However, the End command could move the selection several blank cells past if already in a blank area, so, this is more bullet-proof:
 
   If ActiveCell.Offset(1, 0).Value = "" Then
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.End(xlDown).Offset(1, 0).Select
    End If

Open in new window



Regards,
-Glenn
0
 

Author Comment

by:LLMadden
ID: 40306630
Hi Glenn, I'm very new to macros, can you show me what it would look like in the VBA window?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40306654
Sure.  
 sample VBA project and code
Note that I changed the code!  It wasn't as bullet-proof as I thought!  Here's the updated code if you want to copy and paste.
Sub Next_Blank()
    If ActiveCell.Offset(1, 0) = "" Then
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.End(xlDown).Offset(1, 0).Select
        If ActiveCell.Value <> "" Then
            ActiveCell.End(xlDown).Offset(1, 0).Select
        End If
    End If
End Sub

Open in new window


-Glenn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LLMadden
ID: 40306757
This is perfect, works exactly as I had hoped. This saved me a lot of work. Thank you!!
0
 

Author Closing Comment

by:LLMadden
ID: 40306765
Perfect! Thank  you so much!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40306783
You're welcome.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question