0

I want to know how can I identify the last row of a inactive sheet (without passing control to the inactive sheet) And then how to past data to the row number: 'LastRow+1' from the active sheet. And still keep control to the active sheet.

I am a owner of a small company and want to create a excel sheet to monitor my inventory. I did a littler bit of coding in VB 5 years back but now remember very littler of it. Did write a code for the task mentioned about but my code is of no use :) .... Therefore not pasting it here. Appreciate your help.

Community
  • 1
  • 1
Hitesh Chand
  • 1
  • 1

1 Answers1

0

Use the following vba to find the next row in your inactive sheet:

Dim lastrow as long
lastrow = sheets("sheetname").cells.find("*", range("A1"), , , xlbyrows, xlprevious).row + 1

If you have blank cells at the end add a loop ad use the findnextthis to it:

Dim c as range, lastcell as long 
Set c = sheets("sheetname").cells.find("*", range("A1"), , , xlbyrows, xlprevious)

If trim(c) = "" and not c is nothing then     
    Do         
        Set c = .FindNext(c)         
    Loop While trim(c) <> "" and not c is nothing

End If 
Lastrow = c.row + 1
glh
  • 4,900
  • 3
  • 23
  • 40
  • Hi glh .......... Thanks for the code ......... It is working but partially. The inactive sheet where I am looking for the last row, had some blank spaces in the last few rows. So this code is considering the last blank rows as rows with data and counting then while calculation. Can you please help me in tweaking this code so that it should not count a blank entry row as a row valid row .... – Hitesh Chand Apr 08 '13 at 07:33