?
Solved

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

Posted on 2014-09-05
6
Medium Priority
?
189 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

579 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