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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
380
Office Version
  1. 2007
Platform
  1. Windows
Hello Xosotana,
can you give some data example, and describe ranges a little bit precisely?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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:

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
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)
 

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

ADVERTISEMENT

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
 

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You don't need 2 loops you can embed your new actions in the 1st loop if the data is on the same rows.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

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