Error in finding last row with data

[RCPT]

Hello,

I am having trouble with the code line for looping until last row with data. It returns "Out of range" error, and I already read about it, but I can't find a reason for the error. The sheet name, column and data exist.

This is what I am using




lrow = wsc.ListObjects("SheetName").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row



Anyone can help?

Thank you for your time,

Rute

[arnelgp]

Dim l As ListObject
Set l = ActiveSheet.ListObjects(1)
Debug.Print l.DataBodyRange.Rows.Count + 1 'add 1 for the Header

[RCPT]

Thank you.
I've tried out your code but it still loops to the end of excel and not to the last row with data. That is happening in all of my modules. When it needs to identify the last row for an instruction it just takes forever to run.

Any more suggestions?

Cheers

[georgiboy]

Are you using a range/ table or a pivot table?

[SamT]

Find(Previous, After:=Header. . .)
Or
LRow = Cells(2, 1).End(xlDown).Row

[RCPT]

I'm using tables for all of them

[georgiboy]

Then the below, keep in mind that 'PostHarvest_Plan' is the name of the table and not the name of the worksheet:

lrow = wsc.ListObjects("PostHarvest_Plan").DataBodyRange.Rows.Count + 1

[RCPT]

Find(Previous, After:=Header. . .)
Or
LRow = Cells(2, 1).End(xlDown).Row

Thank you. I've tried that. The result is the same...

[RCPT]

Then the below, keep in mind that 'PostHarvest_Plan' is the name of the table and not the name of the worksheet:

lrow = wsc.ListObjects("PostHarvest_Plan").DataBodyRange.Rows.Count + 1

Thank you, but it persists...

[snb]

As you may have noticed you could speak of 'doubly posted'.
Since this question is part of a code that would better be replaced by somewhat simpler code; see http://www.vbaexpress.com/forum/attachment.php?attachmentid=29684&d=1651142396

[RCPT]

As you may have noticed you could speak of 'doubly posted'.
Since this question is part of a code that would better be replaced by somewhat simpler code; see http://www.vbaexpress.com/forum/attachment.php?attachmentid=29684&d=1651142396

As I understand Advancedfilter cannot be used when there are blanks amongst the data set which is the case of my file.
The problem with finding the last row persists in several modules that's why I posted in different threads.
Thank you for your time

[snb]

You misunderstood the prerequisites for advancedfilter.

[p45cal]

trouble with the code line for looping until last row with dataI'm guessing the trouble is with whatever code you're using for looping; post that.

[Paul_Hossler]

Or post a workbook with the troublesome table and the macro

[RCPT]

Or post a workbook with the troublesome table and the macro

29685
I have tried the two approaches in the modules of this workbook and both fail. The loop goes beyond the number of rows of the tables from which is retrieving data.

The tool I'm building will be filled in by different people and will potentially increment quickly, so I just want to make sure that as the table grows all data is included in the macro.

Should I have another approach?

Thank you,

Rute

[Paul_Hossler]

Is that the correct workbook?

I don't see any ListObjects in it, but the previous suggestions referred to ListObjects


edit

I'm thinking the long loop is because you're playing with the loop counter where <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< marked

I haven't tried to figure out what you're really doing, but maybe something like the uncommented version




' With wsc3
' For trow = 2 To lrow3 'starts at 2 because of the header row
'
' If Len(Trim(wsc4.Cells(crow, 7).Text)) > 0 Then
' wsd.Cells(drow, 4).End(xlUp).Value = .Cells(trow, 7).Text
'
' trow = trow + 1 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' End If
'
' wsd.Cells(drow, 3).Value = .Cells(crow, 4).Value
' drow = drow + 1
' Next crow
' End With

With wsc3
For trow = 2 To lrow3 'starts at 2 because of the header row

If Len(Trim(wsc4.Cells(crow, 7).Text)) > 0 Then
wsd.Cells(drow, 4).End(xlUp).Value = .Cells(trow, 7).Text

Else
wsd.Cells(drow, 3).Value = .Cells(crow, 4).Value
drow = drow + 1
End If
Next crow
End With
End Sub





Probably no difference but setting trow = 3 doesn't really do anything since the loop counter starts it = 2 right away



trow = 3

With wsc3
For trow = 2 To lrow3 'starts at 2 because of the header row

[RCPT]

Is that the correct workbook?

I don't see any ListObjects in it, but the previous suggestions referred to ListObjects


edit

I'm thinking the long loop is because you're playing with the loop counter where <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< marked

I haven't tried to figure out what you're really doing, but maybe something like the uncommented version




' With wsc3
' For trow = 2 To lrow3 'starts at 2 because of the header row
'
' If Len(Trim(wsc4.Cells(crow, 7).Text)) > 0 Then
' wsd.Cells(drow, 4).End(xlUp).Value = .Cells(trow, 7).Text
'
' trow = trow + 1 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' End If
'
' wsd.Cells(drow, 3).Value = .Cells(crow, 4).Value
' drow = drow + 1
' Next crow
' End With

With wsc3
For trow = 2 To lrow3 'starts at 2 because of the header row

If Len(Trim(wsc4.Cells(crow, 7).Text)) > 0 Then
wsd.Cells(drow, 4).End(xlUp).Value = .Cells(trow, 7).Text

Else
wsd.Cells(drow, 3).Value = .Cells(crow, 4).Value
drow = drow + 1
End If
Next crow
End With
End Sub





Probably no difference but setting trow = 3 doesn't really do anything since the loop counter starts it = 2 right away



trow = 3

With wsc3
For trow = 2 To lrow3 'starts at 2 because of the header row

[snb]

Please, please do not, not quote, use the correct reply button.

[RCPT]

29697

This is just one of the problems with the lrow code. In this case I could replicate what is happening in the original workbook in this one.

I just need the rows/columns nominated in the code to be copied to the second sheet.
However what is happening is that it is copying beyond the established table (until row: 1300) and it increases the table indefinitely.

I have tried many different ways to calculate the lrow according to suggestions, but can't make them work.

One of the things that I wondered may be causing problems is that I use formulas with "IF" that ensure that when the main formula does not have the required data it should return "" (eg.: =IF('SheetN'!U1301="";"";'SheetN'!U1301); I'm figuring it's being considered by the Macro as text or value of some kind and not an unaltered cell. Could that be the origin of the problem?

But in all sheets of my original workbook, variances of the same problem occur.

I understand my code is very rudimentary and there are other approaches as the one suggested by snb, but I don't know enough to simplify the coding as proposed (for example without calling the same sheet several times). I also want to have it in such a way that the colleagues that will maintain the tool in the future will be able to see the reference to columns and rows.

Your help is appreciated, because I'm the one in a loop right now.

Cheers,

RT

[Paul_Hossler]

Please, please do not, not quote, use the correct reply button.

@RCPT -- And if it is necessary to include quoted , edit out the non-pertinent text, the macros, text that does not get referred to, etc.


E.G.


Please ... do ... not quote, use ....

[Paul_Hossler]

Your table has 1301 rows, of which only 76 have meaningful (i.e. non-zero) data in column A

The 'multiplier' in column C = 5 for all 1301 rows, including the 'empty' ones at the end

So there are (1301 - 76) x 5 = 6125 additional unneeded rows added to the destination

Try something like this




lrow = wsc.ListObjects("Pharma_ref").DataBodyRange.Rows.Count + 1


With wsc


For crow = 2 To lrow 'starts at 2 because of the header row


Application.StatusBar = "Copy Row " & crow ' <<<<<<<<<<<<<<<<<<<

If wsc.Cells(crow, 1).Value = 0 Then Exit For ' <<<<<<<<<<<<<<<<<<

multiplier = wsc.Cells(crow, 3).Value 'copies the value in column c



For i = 1 To multiplier


wsd.Cells(drow, 5).Value = .Cells(crow, 1).Value
wsd.Cells(drow, 3).Value = .Cells(crow, 2).Value

drow = drow + 1 'increasing the row in worksheet destination


Next i


Next crow


End With

Application.StatusBar = False ' <<<<<<<<<<<<<<<<<<<<

[RCPT]

VBAtests_lrow.xlsm (http://www.vbaexpress.com/forum/attachment.php?attachmentid=29697&d=1651407056)

This is just one of the problems with the lrow code. In this case I could replicate what is happening in the original workbook in this one.

I just need the rows/columns nominated in the code to be copied to the second sheet.
However what is happening is that it is copying beyond the established table (until row: 1300) and it increases the table indefinitely.

I have tried many different ways to calculate the lrow according to suggestions, but can't make them work.

One of the things that I wondered may be causing problems is that I use formulas with "IF" that ensure that when the main formula does not have the required data it should return "" (eg.: =IF('SheetN'!U1301="";"";'SheetN'!U1301); I'm figuring it's being considered by the Macro as text or value of some kind and not an unaltered cell. Could that be the origin of the problem?

But in all sheets of my original workbook, variances of the same problem occur.

I understand my code is very rudimentary and there are other approaches as the one suggested by snb, but I don't know enough to simplify the coding as proposed (for example without calling the same sheet several times). I also want to have it in such a way that the colleagues that will maintain the tool in the future will be able to see the reference to columns and rows.

Your help is appreciated, because I'm the one in a loop right now.

Cheers,

RT

[Aussiebear]

@RCPT, What the hell are you up to? This last post is an exact copy word for word of the post you made Yesterday at 10:47pm. There's nothing new here to show that you have gained from any of the assistance that you have been given. Further more you were politely asked not to fully "Quote", a previous reply unless there's something pertinent within the reply. In fact you were asked to "only Quote" the relevant section to which was causing an issue, but still you insist on filling up a post with non-relevant phraseology. Are you seriously interested in finding a solution or are you one of those people who find some pleasure in plucking feathers from a non existing chicken?

[Paul_Hossler]

Paul has left the building

[RCPT]

Your table has 1301 rows, of which only 76 have meaningful (i.e. non-zero) data in column A

The 'multiplier' in column C = 5 for all 1301 rows, including the 'empty' ones at the end

So there are (1301 - 76) x 5 = 6125 additional unneeded rows added to the destination

Try something like this =

Thank you for your help. I´ve just tried. It worked!
Cheers