#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Does the macro provided by NoSparks do the job ?
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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? |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
You're welcome, glad to have helped.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |
How to turn all blank lines into non-blank for legal forms | sieler | Word | 3 | 04-12-2009 01:38 PM |