How to search for non zero character in MS Excel

I got this unique use case where I was supposed to copy certain number of rows from one master excel to a copy excel. I did it easily by selecting using mouse and pasting it. But, for my bad luck one of the data was in a rows which was after so many empty rows because of which I could not see it. So, I decided to write some logic to detect the last word in last row so that I can copy the data reliably. I cannot copy by doing ctrl A and ctrl C because, the data where I paste is limited in size and many data has to be pasted in a single document. Please help me. In resolving this interesting issue