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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,747
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:

wfsteadman

New Member
Joined
Dec 28, 2016
Messages
15
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))
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,893
Messages
5,834,274
Members
430,273
Latest member
Windrunner

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
Top