How to identify the last cell with content in an Excel Sheet

Reply
Occasional Contributor
Posts: 7

How to identify the last cell with content in an Excel Sheet

Hi there, 

 

I am working with a few macros I inherited that import data from Excel workbooks. Currently, it is required to manually input the RANGE of each sheet to define import data range. I was wondering if there is a way to automatically find the "last cell" that contains content in a sheet. As far as I understand the file is read in line by line, so once there is no content in the next line it should be able to say row 100, column X is the last cell with content. 

 

The goal is to identify the cell "X100" to automatically import it without having to open the file and looking for the last cell with content. 

 

Sample Code: 

 

%Let Excel1Sheet1Range = 'A2:X100';

PROC IMPORT DATAFILE= &Excel1 DBMS=EXCELCS OUT=Sheet1 REPLACE;
		SHEET='Sheet1';
		RANGE=&Excel1Sheet1Range;
		SCANTEXT=YES;
		USEDATE=YES;
		SCANTIME=YES;
	RUN;
Super User
Posts: 11,343

Re: How to identify the last cell with content in an Excel Sheet

Proc import has very little control available.

 

You may want to check if the LIBNAME XLXS works for you.

Some helpful hints

http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Super User
Posts: 19,770

Re: How to identify the last cell with content in an Excel Sheet

Any possibility to not specify the Range at all and let Excel/SAS determine it? If the Range isn't something like A2:X100 that's not possible.
Ask a Question
Discussion stats
  • 2 replies
  • 243 views
  • 0 likes
  • 3 in conversation