Detect tabular list in VBA
 

I am using Office 2003 on Windows XP.

I have a custom program for certain users, but as an error check, the
program needs to determine if the activesheet contains a tabular list of
data. For ease of use and flexibility, I don't want the user to have to
define a list, table, or range.

Most users will know that they need a starting tabular list (which could
span from 1 to 100 columns and any number of rows), so most of the time it
would run correctly. But if someone new, or someone just forgot, it would be
nice if there were some sort of auto-detecting code that could determine this
and generate a custom error message.

Any ideas on how to do this programmatically in VBA?

Thanks much in advance.

Detect tabular list in VBA
 

Are you saying that unless a range is selected an error is required or if
the activesheet is empty of any data?

--
Cheers
Nigel



"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a custom program for certain users, but as an error check, the
program needs to determine if the activesheet contains a tabular list of
data. For ease of use and flexibility, I don't want the user to have to
define a list, table, or range.

Most users will know that they need a starting tabular list (which could
span from 1 to 100 columns and any number of rows), so most of the time it
would run correctly. But if someone new, or someone just forgot, it would
be
nice if there were some sort of auto-detecting code that could determine
this
and generate a custom error message.

Any ideas on how to do this programmatically in VBA?

Thanks much in advance.

Detect tabular list in VBA
 

Good question.

The data the user has in their sheet should, for the most part be in a
rectangular shape and most if not all cells in the rectangle should be
populated. There should not be any stray data in the sheet.

"Nigel" wrote:

Are you saying that unless a range is selected an error is required or if
the activesheet is empty of any data?

--
Cheers
Nigel



"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a custom program for certain users, but as an error check, the
program needs to determine if the activesheet contains a tabular list of
data. For ease of use and flexibility, I don't want the user to have to
define a list, table, or range.

Most users will know that they need a starting tabular list (which could
span from 1 to 100 columns and any number of rows), so most of the time it
would run correctly. But if someone new, or someone just forgot, it would
be
nice if there were some sort of auto-detecting code that could determine
this
and generate a custom error message.

Any ideas on how to do this programmatically in VBA?

Thanks much in advance.

Detect tabular list in VBA
 

Assume you want their data starting in A1

Sub checkData()
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column
set rng1 = Cells(RealLastRow, RealLastColumn)
set rng = Activesheet.Range("A1").currentRegion
if intersect(rng,rng1) is nothing or rng.rows.count = 1 or rng.columns.count
= 1 then
msgbox " could be problems"
exit sub
end if

End Sub

this certainly isn't the end all be all solution. If we knew what was
required, it would be easier to develop something to check.

--
Regards,
Tom Ogilvy

"quartz" wrote:

Good question.

The data the user has in their sheet should, for the most part be in a
rectangular shape and most if not all cells in the rectangle should be
populated. There should not be any stray data in the sheet.

"Nigel" wrote:

Are you saying that unless a range is selected an error is required or if
the activesheet is empty of any data?

--
Cheers
Nigel



"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a custom program for certain users, but as an error check, the
program needs to determine if the activesheet contains a tabular list of
data. For ease of use and flexibility, I don't want the user to have to
define a list, table, or range.

Most users will know that they need a starting tabular list (which could
span from 1 to 100 columns and any number of rows), so most of the time it
would run correctly. But if someone new, or someone just forgot, it would
be
nice if there were some sort of auto-detecting code that could determine
this
and generate a custom error message.

Any ideas on how to do this programmatically in VBA?

Thanks much in advance.