Last Non Empty Cell in Column where there are empty cells mixed in the column

wfsteadman

New Member
Joined
Dec 28, 2016
Messages
15
Greetings folks,
I saw a solution somewhere but I can't find it. I have a column that can be a dynamic length and in the column there could be blank cells
so for example:

Cells A1:A10 could have contents
Cell A11 could be emptry
Cell A12:A15 could have contents
Cell A16:A18 could be empty
Cells A:19:A100 could have contents so I am trying to get it to select cell A101 if that makes sense.

The objective is that I will copy some ranges from different worksheets and will have them pasted into another worksheet (say sheet 9) one after the other. So Sheet 1 I might copy A1;A100 (Keeping the blanks cells that are in the column from example above) then Sheet 2 Copy A1:A150 so on the sheet where they copy to, the contents from sheet 1 will be pasted starting at cell A1 on sheet9 which would mean when sheet 2 is pasted it would start at cell A101 on sheet9 etc....


Any assistance would be appreciated.

Thanks
Wally
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter).
It returns row number of the last non-empty cell in range A1:A1000.

=MAX((A1:A1000<>"")*ROW(A1:A1000))
 
Last edited:
Upvote 0
Is this usable in VBA if I need to hit specific keys to get it to work?

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter).
It returns row number of the last non-empty cell in range A1:A1000.

=MAX((A1:A1000<>"")*ROW(A1:A1000))
 
Upvote 0
Try something like this, paste special values or a plain copy.

Howard


Code:
Option Explicit

Sub A_Copy_Col_1()

Dim LRow As Long
LRow = Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Range("A1:A" & LRow).Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
End Sub




Sub A_Copy_Col_2()

Dim LRow As Long
LRow = Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Range("A1:A" & LRow).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)

Application.CutCopyMode = False
End Sub
 
Upvote 0
the vba code to get the last row of a column is
Code:
Dim lastRw As Long
lastRw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
The 1 is the column number and can be substituted with the column letter enclosed in quote marks, ie. "A"
To get the next available cell below the last row,
Code:
Dim nextRw As Long
nextRw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top