Range SpecialCells Union LastRow

[xman2000]

Hi Partners!

I create a UnionSpecialCells to return LastRow with value (Number, NumericFormulasValue and BlankCells in range)
The Problem is when have a value not in SpecialCells in LastRow (text for example) -- Macro Return the number of this LastRow with values not in SpecialCells in LastRow.

The macro is returning LastRow number Row 51 (text value in this Row, this Row is very LastRow with value but not included im my choose).

I Need LasttRow return number Row 49 (last Numeric) or number Row 50 (last Blanck cell) of sampleFile.
example Msgbox 49 or MsgBox 50

thanks!


Sub LastRowEXCELFORUM2()
Dim Sheet As Worksheet
Dim Lastcolumn As Long
Dim FirstColumn As Long
Dim RngColuna As Range
Set sht = Worksheets("datateste4")
Set RngColuna = sht.Range(sht.Columns(1), sht.Columns(1))
'UNION SPECIALCELLS
'The Problem is when have a value not in SpecialCells in LastRow -- Macro Return the number of LastRow with values not in SpecialCells in LastRow
Dim Xrange3 As Range
'On Error Resume Next
Set Xrange3 = Union(RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeFormulas, Value:=XlSpecialCellsValue.xlNumbers), RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeBlanks), RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeConstants, Value:=XlSpecialCellsValue.xlNumbers))
MsgBox "MSGBOXnovo5UNION5: RANGESPECIAL =" & Xrange3.Address
Dim lastRow60 As Long
lastRow60 = Xrange3(Xrange3.Rows.Count, Xrange3.Cells(1).Column).End(xlDown).End(xlUp).Row
MsgBox "MSGBOXunion60: RANGESPECIAL =" & lastRow60
End Sub

17455

[mancubus]

Sub vbax_57599_special_last_row()

Dim Xrange3 As Range, ar As Range
Dim rngFormulasNumbers As Range, rngBlanks As Range, rngConstantsNumbers As Range
Dim LastRow As Long, LastRowFN As Long, LastRowBlnks As Long, LastRowCN As Long

With Worksheets("datateste4")
With .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
Set Xrange3 = Union(.SpecialCells(xlCellTypeFormulas, xlNumbers), .SpecialCells(xlCellTypeBlanks), .SpecialCells(xlCellTypeConstants, xlNumbers))
'Set Xrange3 = Union(.SpecialCells(-4123, 1), .SpecialCells(4), .SpecialCells(2, 1)) 'same as above, but shorter
Set rngFormulasNumbers = .SpecialCells(xlCellTypeFormulas, xlNumbers) '.SpecialCells(-4123, 1)
Set rngBlanks = .SpecialCells(xlCellTypeBlanks) '.SpecialCells(4)
Set rngConstantsNumbers = .SpecialCells(xlCellTypeConstants, 1) '.SpecialCells(2, 1)
End With
End With

LastRow = 0
For Each ar In Xrange3.Areas
If ar.Rows(ar.Rows.Count).Row > LastRow Then LastRow = ar.Rows(ar.Rows.Count).Row
Next ar


LastRowFN = 0
For Each ar In rngFormulasNumbers.Areas
If ar.Rows(ar.Rows.Count).Row > LastRowFN Then LastRowFN = ar.Rows(ar.Rows.Count).Row
Next ar


LastRowBlnks = 0
For Each ar In rngBlanks.Areas
If ar.Rows(ar.Rows.Count).Row > LastRowBlnks Then LastRowBlnks = ar.Rows(ar.Rows.Count).Row
Next ar


LastRowCN = 0
For Each ar In rngConstantsNumbers.Areas
If ar.Rows(ar.Rows.Count).Row > LastRowCN Then LastRowCN = ar.Rows(ar.Rows.Count).Row
Next ar

Debug.Print "Last Row : " & LastRow
Debug.Print "Last Row Formulas Numbers: " & LastRowFN
Debug.Print "Last Row Blanks: " & LastRowBlnks
Debug.Print "Last Row Constants Numbers: " & LastRowCN

End Sub

[mancubus]

or



Sub vbax_57599_special_last_row_shorter()

Dim ar As Range
Dim LastRow As Long, LastRowFN As Long, LastRowBlnks As Long, LastRowCN As Long

With Worksheets("datateste4")
With .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
LastRow = 0
For Each ar In Union(.SpecialCells(-4123, 1), .SpecialCells(4), .SpecialCells(2, 1)).Areas
If ar.Rows(ar.Rows.Count).Row > LastRow Then LastRow = ar.Rows(ar.Rows.Count).Row
Next ar


LastRowFN = 0
For Each ar In .SpecialCells(-4123, 1).Areas
If ar.Rows(ar.Rows.Count).Row > LastRowFN Then LastRowFN = ar.Rows(ar.Rows.Count).Row
Next ar

LastRowBlnks = 0
For Each ar In .SpecialCells(4).Areas
If ar.Rows(ar.Rows.Count).Row > LastRowBlnks Then LastRowBlnks = ar.Rows(ar.Rows.Count).Row
Next ar

LastRowCN = 0
For Each ar In .SpecialCells(2, 1).Areas
If ar.Rows(ar.Rows.Count).Row > LastRowCN Then LastRowCN = ar.Rows(ar.Rows.Count).Row
Next ar
End With
End With

Debug.Print "Last Row : " & LastRow
Debug.Print "Last Row Formulas Numbers: " & LastRowFN
Debug.Print "Last Row Blanks: " & LastRowBlnks
Debug.Print "Last Row Constants Numbers: " & LastRowCN

End Sub

[mancubus]

choose what you need.

use Debug.Print to display the results in VBE's Immediate window.
use MsgBox to display the results in message box.

[xman2000]

Hi, Mancubus, thank you very mutch!

but in your way have same problem, i want if exists some value not in UnionSpecialCells, the Row number need to be a previous Row number or Row of the Last Value (numeric).
i create a sample file with your code, and the macro return LastRow 52.
Last ValueNumeric before ValueNotInSpecial Row 49
Last blankCell before ValueNotInSpecial Row 50
Last ValueNotInSpecialCells (text) Row 51
Last Value(Numeric) after ValueNotInSpecial Row 52

If put some ValueINSpecial after the ValueNotInSpecial the macro return LastRow number of ValueNotInSpecial.

Mancubus, you plays with my Original sample file?
In MsgBox of range limits shows various AreasOfRanges, and the Area i need is "A1:A50", LastRow this Area, Row 50 (blank), or Row 49 (value numeric).
Im not want not want values after, After the Row with ValueNotInSpecial (text value).

The standar results of SpecialCells is the result this macros, but i am need other result, macro not exceeds limit of last ValueInSpecial (numeric or blank) before next ValueNotInSpecial (text value).

this macro not work for this.

thanks!
see Your code in sample file.

17462

[mancubus]

?


Sub vbax_57599_very_special_last_row()

Dim LookUpRng As Range, Xrange3 As Range, ar As Range, cll As Range
Dim FirstRowNotSpecial As Long, LastRow As Long, LastRowFN As Long, LastRowBlnks As Long, LastRowCN As Long

With Worksheets("DATAteste4")
Set LookUpRng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
With LookUpRng
Set Xrange3 = Union(.SpecialCells(-4123, 1), .SpecialCells(4), .SpecialCells(2, 1))
End With
End With

For Each cll In LookUpRng
If Intersect(cll, Xrange3) Is Nothing Then
FirstRowNotSpecial = cll.Row
Exit For
End If
Next cll

With Worksheets("DATAteste4")
With .Range("A2:A" & FirstRowNotSpecial - 1)
LastRow = 0
For Each ar In Union(.SpecialCells(-4123, 1), .SpecialCells(4), .SpecialCells(2, 1)).Areas
If ar.Rows(ar.Rows.Count).Row > LastRow Then LastRow = ar.Rows(ar.Rows.Count).Row
Next ar

LastRowFN = 0
For Each ar In .SpecialCells(-4123, 1).Areas
If ar.Rows(ar.Rows.Count).Row > LastRowFN Then LastRowFN = ar.Rows(ar.Rows.Count).Row
Next ar

LastRowBlnks = 0
For Each ar In .SpecialCells(4).Areas
If ar.Rows(ar.Rows.Count).Row > LastRowBlnks Then LastRowBlnks = ar.Rows(ar.Rows.Count).Row
Next ar

LastRowCN = 0
For Each ar In .SpecialCells(2, 1).Areas
If ar.Rows(ar.Rows.Count).Row > LastRowCN Then LastRowCN = ar.Rows(ar.Rows.Count).Row
Next ar
End With
End With

Debug.Print "First Row Not In Special : " & FirstRowNotSpecial
Debug.Print "Last Row : " & LastRow
Debug.Print "Last Row Formulas Numbers: " & LastRowFN
Debug.Print "Last Row Blanks: " & LastRowBlnks
Debug.Print "Last Row Constants Numbers: " & LastRowCN


End Sub

[xman2000]

Hi, Mancubus,

your approach is good, but if delete the "text value" in the column the macro gives error.

iam working with aproach of "Areas", i need the Last Row of Area of small cell between of all areas, i think!
example: Total of Areas = 2 LastRow of Area1 = 49 LastRow fo Area2 = 71 then RESULT = 49.
but if some Area desappear, macro gives Error or wrong result.
I am need too automatize the others routines of Areas.Count.

iam put in the forum later the progress.
i need more help.
thanks.

[mancubus]

i cant follow you, nor can i understand your requirement.

perhaps explaining your ultimate goal will help us understand what you are after.

[xman2000]

Hi Mancubus, iam upload the sample next hours or tomorrow, thanks
here now is 20:50 hours PM.

[xman2000]

Hi, Mancubus and all Partners, i think i have sucesss, but not is BulletProof.
my Error Handling not works, i need entire macro stop if column RngColuna is empty (no data, no formulas).

Put the correct code in the sample file below for me, please, iam not have sucess. Thanks.

thank you.
i now need include error Handling to stop entire macro (exit sub or end) like:


Dim Xrange3 As Range'''''On Error Resume Next


If IsEmpty (RngColuna) Then


MsgBox "NOT EXISTS DATA IN RANGE RngColuna: NOT EXISTS DATA IN RANGE RngColuna="

Exit Sub
ELSE


Set Xrange3 = Union(RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeFormulas, Value:=XlSpecialCellsValue.xlNumbers), RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeBlanks), RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeConstants, Value:=XlSpecialCellsValue.xlNumbers))
MsgBox "1MSGBOXnovo5UNION5: XRANGE3address =" & Xrange3.Address

CODE..CODE. CODE..

END IF




my sample file
17481

[xman2000]

Hi, Mancubus and all Partners, more progress, i done the error routine parcial. But i need do other thing of routine, for the each parameter SpecialCells (number, fomulas, blanks), verify if exists one or zero ocurrences and if zero then "msgbox" or "Set Xrange = other parameters "

my code works but gives error if no has the type of data (FormulaNumbers) in the column. i need verify if exists one or zero ocurrences of FormulaNumbers to set Xrange to other parameters and do this after with other parameters (numbers and blanks)

thank you.
See the sample file below!
i now need include error Handling to stop entire macro (exit sub or end) like:
PS: This gives error if no has the type of data FormulaNumbers in the column.



'[XMAN2000 MANCUBUS HERE THE PROBLEM NEED VERIFY IF EXISTS THE rngFormulasNumbers, IF NO, THEN MSBOX]
If Application.WorksheetFunction.CountA(Columns(1).SpecialCells(xlCellTypeForm ulas, xlNumbers)) = 0 = True Then
MsgBox "NOT EXISTS SpecialCells(xlCellTypeFormulas, xlNumbers)): NOT EXISTS SpecialCells(xlCellTypeFormulas, xlNumbers))="
Exit Sub
Else
'TEST msgbox]
MsgBox 6 + 1
End If

'This code i use to verify if Entire column is empty and works fine


'UNION SPECIALCELLS
Dim Xrange3 As Range
If Application.WorksheetFunction.CountA(Columns(1)) = 0 = True Then
MsgBox "NOT EXISTS DATA IN RANGE RngColuna: NOT EXISTS DATA IN RANGE RngColuna="
Exit Sub
Else
Set Xrange3 = Union(RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeFormulas, _
Value:=XlSpecialCellsValue.xlNumbers), RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeBlanks), _
RngColuna.SpecialCells(Type:=XlCellType.xlCellTypeConstants, Value:=XlSpecialCellsValue.xlNumbers))
MsgBox "1MSGBOXnovo5UNION5: XRANGE3address =" & Xrange3.Address


17484

[xman2000]

Hi Mancubus and Partners,

i need count and compare quantity of NumericCells (include blanks) and EmptyCells (empty of values and formulas).
PseudoCode : ''['''If quantity of NumericCells > quantity of EmptyCells -- then IsNumber - then Run The Code]

PS: Excel see blanks and empty cells like True NumericCells. I want True NumberValues and Not blank or empty.
This code usind "Count( )" Not Works and "CountA( )" Not works too:

If exists True NumberVAlue in column - then Run The Code - if not - then exit sub.


If Application.WorksheetFunction.Count(IsNumeric(Columns(1))) > 0 = True And (Application.WorksheetFunction.Count(IsNumeric(Columns(1))) - Application.WorksheetFunction.Count(IsEmpty(Columns(1)))) > 0 = True Then



'''[TEST msgbox]
MsgBox "EXISTS Numeric: EXISTS Numeric="

Else

'''[TEST msgbox]
MsgBox "NOT EXISTS Numeric: NOT EXISTS Numeric="

Exit Sub
End If

[Aussiebear]

Have you had a look at the ISEmpty function?

[xman2000]

Have you had a look at the ISEmpty function?

Yes, Aussiebear, look my message above, and below, but i not able to combine IsEmpty with Count all empty cells of Column without loop then like "CountA( )"

i have the same problem with IsBlank and the others functions of data types (IsText, HasFormula) to count entire column in one shot.

look:


Application.WorksheetFunction.Count(IsEmpty(Columns(1)))) > 0 = True Then

[xman2000]

Hi, Mancubus, AussieBear, and Partners!

i am close this thread without solve all problems but solve the initial subjects keyowrds.
i am open new thread to solve other subjects keywords.
iam put here below the new sample file with new progress and working!

thank you very mutch!

17508

[Aussiebear]

i am close this thread without solve all problems but solve the initial subjects keyowrds.
i am open new thread to solve other subjects keywords.


The great difficulty with this thread is not just that you often talk in riddles as in the above quote, but that you seem to delight in making complex issues with the logic of your programming intention. If there had been greater clarity...... who knows perhaps a solution may have been found. I have marked this thread as UNSOLVED since a solution has not been achieved at this point in time. To do otherwise would be misleading.

[xman2000]

Hi, Aussiebear,

Kenneth Hobs solved my thread with this code:



Sub sIsText()
MsgBox Evaluate("=SUMPRODUCT(--ISTEXT(A:A))")
End Sub


Sub Test_CountColIfText()
MsgBox CountColIfText([A1])
End Sub


Function CountColIfText(aRange As Range) As Long
CountColIfText = Evaluate("=SUMPRODUCT(--ISTEXT(" & _
Columns(aRange.Column).Address & "))")
End Function

Yes, my codes are complex to solve problemns in few lines, one shot, and to be more dinamic, versatile and powerfull, my english is poor.

Now, i can use IsEmpty, IsBlank, IsNumber, etc combined with Evaluate to count entire column.
thanks!

[Aussiebear]

Kenneth Hobs solved my thread with this code:


But not within this thread. For someone else to read this thread is would seem to have been without a solution.

[xman2000]

Hi Aussiebear,

I forgot to explain how I solved the initial issue that was to get the last Row of range SpecialCells.
I'll explain now.
UnionSpecialCells creates one or more Regions that can change the number according to the quantity and type of values ​​in the column, so it is more difficult to obtain the wanted line.
First it is necessary to know which Region has the wanted range.

From what I remember, the Region that interests me is the cell closest to the Top of the Worksheet, so I made a calculation to get the number of areas, and the last line of each area, and then I created a calculation to get the line Of the column to which the last line of each area corresponds and a calculation of subtraction to determine which of the Rows of Regions is the smallest Row, and that smaller row is the LastRow desired.

I did this calculation only for two Regions because I do not have time now to make a bigger code.
I had never worked with Regions but I saw that the question was there and I researched it and found the answer.

At the end of the macro I put a loop to check from the LastRow that was already found, if the Row had numeric value or not, or if it was empty cell, and then return in the loop until the next cell that has numeric value, because My SpecialCells includes blank cells strategically but the desired result is the row of the last numeric value of the first Area.

The utility of this code is to dynamically map a spreadsheet or column that has the desired numerical data for plotting graphs in my case, but which are in Worksheet that also have texts and other things, and so the code will separate for me only the Region that desire.

The SpecialCells makes an error when the spreadsheet is empty or does not have any of the desired values ​​in the column (blank cells, formulas, numbers) and for this reason I had to create a check if the column has all these values ​​if it does not have the macro Establishes the UnionCells with the values ​​that exist, or, if none of the values ​​exist, Exit Sub.

Kenneth Hobs helped me solve the problem of verifying the existence of values in order to avoid errors in the UnionSpecials.
I put down the sample file solved.
thank you all people.

17519