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:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Tetra201

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

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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