Return to Answer

This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.

IMPORTANT NOTE: This MUST be called as a Sub if it is a function that is called from a cell then, it will not work. (because excel uses the active range to perform the start of the SpecialCells I believe).

I have tested this code with your picture of cells and it works well.

It also works with other versions of cell combinations and locations that I have tested.

The reason to use the following code is because it is simple and can save valuable programming time. The SpecialCells Method is reliable if used correctly just like any other programming language and function. It is worth a try and also worth timing with your larger data.

In addition, the OP also uses SpecialCells in his code, just not the same way that I have.

I hope this helps.

 Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("A1") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell)).Address End Sub 

This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.

IMPORTANT NOTE: This MUST be called as a Sub if it is a function that is called from a cell then, it will not work. (because excel uses the active range to perform the start of the SpecialCells I believe).

I have tested this code with your picture of cells and it works well.

It also works with other versions of cell combinations and locations that I have tested.

The reason to use the following code is because it is simple and can save valuable programming time. The SpecialCells Method is reliable if used correctly just like any other programming language and function. It is worth a try and also worth timing with your larger data.

In addition, the OP also uses SpecialCells in his code, just not the same way that I have.

I hope this helps.

 Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("A1") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell, xlTextValues)).Address End Sub 

This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.

I have tested this code with your picture of cells and it works well.

It also works with other versions of cell combinations and locations that I have tested.

The reason to use the following code is because it is simple and can save valuable programming time. The SpecialCells Method is reliable if used correctly just like any other programming language and function. It is worth a try and also worth timing with your larger data.

In addition, the OP also uses SpecialCells in his code, just not the same way that I have.

I hope this helps.

 Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("A1") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell)).Address End Sub 

This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.

IMPORTANT NOTE: This MUST be called as a Sub if it is a function that is called from a cell then, it will not work. (because excel uses the active range to perform the start of the SpecialCells I believe).

I have tested this code with your picture of cells and it works well.

It also works with other versions of cell combinations and locations that I have tested.

The reason to use the following code is because it is simple and can save valuable programming time. The SpecialCells Method is reliable if used correctly just like any other programming language and function. It is worth a try and also worth timing with your larger data.

In addition, the OP also uses SpecialCells in his code, just not the same way that I have.

I hope this helps.

 Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("A1") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell)).Address End Sub 

This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.

I have tested this code with your picture of cells and it works well. The reason to use the following code is because it is simple and can save valuable programming time. The SpecialCells Method is reliable if used correctly just like any other programming language and function. It is worth a try and also worth timing with your larger data.

I hope this helps.

 Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("A1") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell)).Address End Sub 

This should work. It's pretty simple method. I cannot test with your sheet as mine is not nearly that large, but you can try it.

I have tested this code with your picture of cells and it works well.

It also works with other versions of cell combinations and locations that I have tested.

The reason to use the following code is because it is simple and can save valuable programming time. The SpecialCells Method is reliable if used correctly just like any other programming language and function. It is worth a try and also worth timing with your larger data.

In addition, the OP also uses SpecialCells in his code, just not the same way that I have.

I hope this helps.

 Option Explicit Sub RunIT() 'Input range must be the first cell of where the data starts GetAllCells Range("A1") End Sub Public Sub GetAllCells(rngInStartingRange As Range) Dim strTemp As String strTemp = Range(rngInStartingRange, rngInStartingRange.SpecialCells(xlLastCell)).Address End Sub