I want to find the last Non Blank Row

xosotana

New Member
Joined
Jan 1, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
Platform
  1. Windows
  2. Mobile
  3. Web
I want to find the the last Four NON-BLANK Row and copy the cell value, to Column B,C.D E on the 2nd Sheet..


CODE

Sub FindTheLastFour()
'Finds the last four non-blank cell end xlUp
Dim lRow As Long
For i = 3 To 20
'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Next i
MsgBox "Last Row: " & lRow
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hello Xosotana,
can you give some data example, and describe ranges a little bit precisely?
 
Upvote 0
Maybe this way
VBA Code:
Sub FindTheLastFour()
Dim lRow As Long, i As Long
lRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
For i = 3 To 20
'your code in here
Next i
MsgBox "Last Row: " & lRow
End Sub
 
Upvote 0
AND depending on what you are trying to do with your code, this line will either need to be
VBA Code:
For i = 3 To lRow

OR

VBA Code:
For i = lRow to 3 step -1
You also need to ensure you declare ALL variables.....i should be declared as long
 
Upvote 0
to Column B,C.D E on the 2nd Sheet..
You don't state what row you are putting the values in, the below assumes row 2 and the cells that you are copying from are all in column A. Change the sheet names to suit.

VBA Code:
Sub FindTheLastFour()
    Dim lRow As Long, x As Long, i As Long
    
    x = 1
    lRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = lRow To 2 Step -1
        If x > 4 Then Exit Sub
        If Sheets("Sheet1").Cells(i, "A") <> "" Then
            Sheets("Sheet2").Cells(2, x + 1).Value = Sheets("Sheet1").Cells(i, "A").Value
            x = x + 1
        End If
    Next i

End Sub
 
Last edited:
Upvote 0
Hello Xosotana,
can you give some data example, and describe ranges a little bit precisely?
1609542433646.png

i want to copy the last four values (458, 150,2568,25) to the second sheet (1B,1C,1D,1E)
 
Upvote 0
You don't state what row you are putting the values in, the below assumes row 2 and the cells that you are copying from are all in column A. Change the sheet names to suit.

VBA Code:
    Dim lRow As Long, x As Long, i As Long
 
    x = 1
    lRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = lRow To 2 Step -1
        If x > 4 Then Exit Sub
        If Sheets("Sheet1").Cells(i, "A") <> "" Then
            Sheets("Sheet2").Cells(2, x + 1).Value = Sheets("Sheet1").Cells(i, "A").Value
            x = x + 1
        End If
    Next i

End Sub
thanks for your help this work
 
Upvote 0
Happy it helped and welcome to the forum.
can we run two For loop the same time. when i run the code it run only the second For loop, it will jump or skip the first loop

VBA Code:
For i = DenT To 15 Step -1
        If x > 4 Then Exit Sub
            If Sheets("36426 - " + newDate).Cells(i, "C") <> "" Then
                    Sheets("36426" + Format(Date, " - mmm")).Cells(9, x + 1).Value = Sheets("36426 - " + newDate).Cells(i, "C").Value
                x = x + 1
               End If
            For n = MosT To 15 Step -1
            If t > 4 Then Exit Sub
                If Sheets("36426 - " + newDate).Cells(n, "D") <> "" Then
                    Sheets("36426" + Format(Date, " - mmm")).Cells(9, t + 5).Value = Sheets("36426 - " + newDate).Cells(n, "D").Value
                    t = t + 1
                End If
            Next n
        Next i
 
Upvote 0
You don't need 2 loops you can embed your new actions in the 1st loop if the data is on the same rows.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top