VBA - How to identify last active line, when has formula

Asked

Viewed 666 times

0

The code below identifies the last active line of the worksheet.
When column A is filled in manually it works. But when I put a formula to fill that column according to another spreadsheet. The code keeps running.

For example: the spreadsheet has data up to line 46, but the execution only stops when it reaches line 78, until where I dragged the formula.

lUltimaLinhaAtiva = Worksheets("Painel").Cells(Worksheets("Painel").Rows.Count, 1).End(xlUp).Row
excel vba

  • Select all lines in "white" and delete them to see, it should work.

  • I can’t do that. Because depending on the filter I use in the other sheet the number of rows can increase or decrease

1 answer

0


Considering a datasheet called "Data" and a with formulas "Panel"

Enter a condition formula =SE() in the formula to leave the line empty if there is no data in the sheet "Data" with:

=SE(Dados!A1, FÓRMULA_CÁLCULO(A1), "")

In which FÓRMULA_CÁLCULO() is the formula used for the calculations in the worksheet "Panel"

Then the Last Line can be obtained with:

Dim lUltimaLinhaAtiva  As Long
lUltimaLinhaAtiva = Worksheets("Painel").Cells.Find("*", Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.