Error in finding last row with data

[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

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