Last Blank cell

[vmjan02]

The code is all perfect but it is not applying the formula till the end, not sure what is wrong.

this line from the code is not picking the lastnonblank cell dut to this the formual is not applied till last

lastNonBlankRow = destinationRange.Columns(destinationColumn).Cells(destinationRange.Rows.Count).End(xlUp).Row

    Dim lastRowM As Long
    Dim ws As Worksheet
    Dim currentWorkbook As Workbook
    Dim destinationColumn As Integer
    Dim destinationRange As Range
    Dim lastNonBlankRow As Long
   
    Set currentWorkbook = ThisWorkbook
    
    destinationColumn = 1
    
    Set destinationRange = currentWorkbook.Sheets("ALPCal Final").Cells(1, destinationColumn)
    
    currentWorkbook.Sheets("ALPCal Final").Range("A1:BA509728").Clear

currentWorkbook.Sheets("ALPCal Final").Select
Application.Wait (Now + TimeValue("0:00:02"))


Range("A1").Select

For Each ws In currentWorkbook.Sheets
        If Left(ws.Name, 3) = "ALp" Then
            ' Find the last row in the ALp sheet
            lastRowM = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

            ' Copy data from ALp sheet to the "ALPCal Final" sheet
            ws.Range("C1:C" & lastRowM).Copy destinationRange.Cells(1, destinationColumn)

            ' Remove duplicates in the destination column
            destinationRange.Columns(destinationColumn).RemoveDuplicates Columns:=1, header:=xlYes
            
            'find the lastnonblank cell from current destinationColumn
            lastNonBlankRow = destinationRange.Columns(destinationColumn).Cells(destinationRange.Rows.Count).End(xlUp).Row
            
            ' Apply the COUNTIF formula to count occurrences up to the last non-blank row in the current column
            With destinationRange.Offset(1, destinationColumn).Resize(IIf(lastNonBlankRow > 1, lastNonBlankRow - 1, 1), 1)
            .Formula = "=COUNTIF('" & ws.Name & "'!C:C, A2)"
            .Value = .Value
            End With
            
           ' Move to the next column for the next ALp sheet
            destinationColumn = destinationColumn + 3
            destinationRange.Columns(destinationColumn).Select
        End If
        lastRowM = 0
        lastNonBlankRow = 0
        
    Next ws

    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

ALP Auotmation Weekly - V1.xlsm
	A	B	C	D	E	F	G	H
1	Speed_char			Speed_char			Speed_char
2	300	168105		300	169582		300	20839
3	100			100			100
4	500			500			500
5	200			200			200
6	1.02400000000000E 003			10			1.02400000000000E 003
7	20			4			10
8	10			1.02400000000000E 003			2
9	40			50			40
10	50			40			50
11	80			80			20
12	25			20			80
13	2			2			4
14	4			25			25
15	30			2.50E-01			1
16	350						2.50E-01
17	1						5.00E-01
ALPCal Final

 

[jkpieterse]

Like this:

lastNonBlankRow = destinationRange.Columns(destinationColumn).Cells(currentWorkbook.Sheets("ALPCal Final").Rows.Count).End(xlUp).Row

 

[vmjan02]

Like this:

lastNonBlankRow = destinationRange.Columns(destinationColumn).Cells(currentWorkbook.Sheets("ALPCal Final").Rows.Count).End(xlUp).Row

Tryied this but same result no luck

 

[jkpieterse]

I suspect there is a flaw in how you are using the column number. Try this code:

Sub Examples()
    Dim destinationColumn As LongPtr
    Dim destinationRange As Range
    
    destinationColumn = 2
    MsgBox Worksheets("Sheet1").Columns(destinationColumn).Address
    Set destinationRange = Worksheets("Sheet1").Columns(destinationColumn)
    MsgBox destinationRange.Columns(destinationColumn).Address
End Sub

 

[vmjan02]

I suspect there is a flaw in how you are using the column number. Try this code:

Sub Examples()
    Dim destinationColumn As LongPtr
    Dim destinationRange As Range
   
    destinationColumn = 2
    MsgBox Worksheets("Sheet1").Columns(destinationColumn).Address
    Set destinationRange = Worksheets("Sheet1").Columns(destinationColumn)
    MsgBox destinationRange.Columns(destinationColumn).Address
End Sub

I modified this part .End(xlUp) to .End(xlDown)

lastNonBlankRow = destinationRange.Columns(destinationColumn).Cells(destinationRange.Rows.Count).End(xlUp).Row

this is working now

 

[Alex Blakenburg]

destinationRange was 1 cell (A1), adding .Columns to it still leaves it as 1 cell.
rows.count of 1 cell is 1 row so it is still A1.
xlup on A1 has nowhere to go.

Something like this would work:

    With currentWorkbook.Sheets("ALPCal Final")
        lastnonblankrow = .Cells(.Rows.Count, destinationColumn).End(xlUp).Row
    End With

 

[jkpieterse]

@Alex Blakenburg but yours is different from his, as he is working relative to the destinationRange and you are working relative to cell A1.

 

[Alex Blakenburg]

@Alex Blakenburg but yours is different from his, as he is working relative to the destinationRange and you are working relative to cell A1.

Is it though ? Is this not A1 ?

destinationColumn = 1 
Set destinationRange = currentWorkbook.Sheets("ALPCal Final").Cells(1, destinationColumn)

And being a single cell the following returns just that 1 cell (A1)

destinationRange.Columns(destinationColumn).Cells(destinationRange.Rows.Count)