Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-13-2020, 11:30 PM
Dave T Dave T is offline Go to second last blank row Windows 7 64bit Go to second last blank row Office 2013
Advanced Beginner
Go to second last blank row
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default Go to second last blank row

Hello All,

I am sure this question has been asked many times before and I may just be using the wrong search terms…

I have various workbooks where I have a row at the end with the text END across each column. I use this row as a guide where my formatting and formulas etc. ends (this could equally be the final row where my totals formula is located.). When I am close to this row I insert copies of the rows, to maintain my formulas, totals formulas, formatting, etc.

I know about using .end(xlup) to find the last row, but how can this be run twice ???



For example, the first time it is run in column A it will go up and find the text string END or TOTALS.
How do I get it run again the find the next last blank cell in column A (no formula used in column A).
I would like to be able to click on a button and the last cell in column A above the row with the text or formulas is selected.

Thanking you in advance,
Dave T
Reply With Quote
  #2  
Old 05-14-2020, 10:09 AM
Logit Logit is online now Go to second last blank row Windows 10 Go to second last blank row Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

You should be able to run that macro as many times as desired without error.

If you already have code that you are using, perhaps posting the code and an example workbook that does not contain sensitive / personal data would assist in rectifying your issue.
Reply With Quote
  #3  
Old 05-14-2020, 05:20 PM
NoSparks NoSparks is offline Go to second last blank row Windows 10 Go to second last blank row Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Perhaps this
Code:
Sub Dave_T()
    Dim lr As Long, i As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lr To 1 Step -1
        If Cells(i, "A") = "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next i
End Sub
Reply With Quote
  #4  
Old 05-14-2020, 05:40 PM
Dave T Dave T is offline Go to second last blank row Windows 7 64bit Go to second last blank row Office 2013
Advanced Beginner
Go to second last blank row
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

Hello Logit,

I have attached a workbook with some dummy data and a few macros I currently use.
Column E contains some dummy formulas and conditional formatting.

As I progressively fill column A with data, I would see my column end markers and be aware of that I would need to copy more rows.

I have left a few blank cells in column A as an example.
What I meant by second last row in my workbook would mean that cell A27 would be selected (or whatever the second last cell is).

Regards, Dave T
Attached Files
File Type: xlsm Find Second Last Blank Cell.xlsm (21.5 KB, 11 views)
Reply With Quote
  #5  
Old 05-14-2020, 06:26 PM
Logit Logit is online now Go to second last blank row Windows 10 Go to second last blank row Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Does the macro provided by NoSparks do the job ?
Reply With Quote
  #6  
Old 05-14-2020, 06:39 PM
Dave T Dave T is offline Go to second last blank row Windows 7 64bit Go to second last blank row Office 2013
Advanced Beginner
Go to second last blank row
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

Hello Logit,


No the macro provided by NoSparks does not select cell A27.
It has the same functionality as the one on the attached workbook that I have called M3_SecondLastBlankCell (it selects the cell above the last row, i.e. cell A29).


I do appreciate your help though.
Reply With Quote
  #7  
Old 05-14-2020, 06:57 PM
Logit Logit is online now Go to second last blank row Windows 10 Go to second last blank row Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

You could edit NoSparks macro like below ... so long as the term END will always be three rows under the last data entry.

Code:
Option Explicit

Sub Dave_T()
    Dim lr As Long, i As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lr To 1 Step -3
        If Cells(i, "A") = "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next i
End Sub
Reply With Quote
  #8  
Old 05-14-2020, 07:57 PM
Dave T Dave T is offline Go to second last blank row Windows 7 64bit Go to second last blank row Office 2013
Advanced Beginner
Go to second last blank row
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

Thank you Logit,


What I was trying to explain in my example was that the numbers of blank cells above the cell with END will be of a random nature and copied rows may be inserted at any time.
To me it seems like the first time .End(xlup) is run it finds the cell below END. If the cell above END is blank, run again .End(xlup) to find the next last blank cell (in my example A27). The number of blank cells above END may very well be not consistent.
Reply With Quote
  #9  
Old 05-14-2020, 08:02 PM
Logit Logit is online now Go to second last blank row Windows 10 Go to second last blank row Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

I'm getting REAL confused now. Sorry but your description for me is causing problems.

Are you wanting the macro to find the blank cell immediately above END ... or immediately below END ... all the time?
Reply With Quote
  #10  
Old 05-14-2020, 08:22 PM
NoSparks NoSparks is offline Go to second last blank row Windows 10 Go to second last blank row Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Beyond me how you figure A27 is the second blank...
it's the third blank from the bottom and sixth blank from the top.

Try this
Code:
Sub Dave_T_v2()
    Dim lr As Long, i As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lr To 1 Step -1
        If Cells(i, "A") = "" Then
            If Cells(i - 1, "A") <> "" Then
                Cells(i, "A").Select
                Exit For
            End If
        End If
    Next i
End Sub
If that's not what you're after you'll need to explain a little better.
Reply With Quote
  #11  
Old 05-14-2020, 08:43 PM
Dave T Dave T is offline Go to second last blank row Windows 7 64bit Go to second last blank row Office 2013
Advanced Beginner
Go to second last blank row
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

Hello NoSparks,

Perfect.
Thank you very much for persisting with me, I really appreciate all your help.

What I was trying to explain and show, was that there could be other blank cells in column A and that using .End(xldown) would not work (as you put it "blank cells from the top"). Various blank cells in column A could still be waiting on more information before being filled in.

The second last cell in column A is where I am up to when working my way down and filling in the information across the row.

Once again, thanks to you and Logit for all your help.

Regards, Dave T
Reply With Quote
  #12  
Old 05-14-2020, 10:26 PM
NoSparks NoSparks is offline Go to second last blank row Windows 10 Go to second last blank row Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

You're welcome, glad to have helped.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Go to second last blank row Delete comma and space after blank merged value. Also remove Previous space and word before blank Alex1s85 Mail Merge 4 01-18-2020 11:30 PM
Leaving a Blank Field Blank migman Mail Merge 10 08-29-2017 03:28 AM
Go to second last blank row Formulato say if cell is blank do this, if not blank do this. mbesspiata Excel 1 01-17-2015 05:02 AM
Page numberring 1 - blank - 2 - blank etc Intern PowerPoint 0 09-02-2011 01:16 AM
Go to second last blank row How to turn all blank lines into non-blank for legal forms sieler Word 3 04-12-2009 01:38 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft