BTW, it 3:40am here and so I need to 'bag some Zs' before doing much
more...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Fri, 27 Nov 2015 03:41:27 -0500 schrieb GS:

BTW, it 3:40am here and so I need to 'bag some Zs' before doing much
more...

then it is time to go to bed. Here it is 9:45 am.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi all,

The sheets are single letter A to G on sample workbook, A to Z in real workbook.

I did not submit the code: For i = Asc("A") To Asc("G").

Another question, please.

Summary sheet has a Table, which seems to me to be column A. I can go to name box and Table1 is listed.

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

Are there common rules on how to copy to a table as we are here. I anticipate some problems here. If you delete the data in the table/filtered area, the next copy goes to the first empty row below the table. That could be 50 + rows down.

If you delete all the data filled ROWS of the table, then you have the Headers and a empty 1st row of the table, and the copy goes to the first row below that empty table row.

Perhaps, the table and filter should be "Turned Off" and old data deleted, to leave a row of Headers, then do the copy, then reinstate the table and filter???

Howard

Hi Howard,

Am Fri, 27 Nov 2015 01:10:34 -0800 (PST) schrieb L. Howard:

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

I don't know exactly how it is named in the english Version. But try:
Right clikc to column A = Table = Convert to Range


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I don't know exactly how it is named in the english Version. But try:
Right clikc to column A = Table = Convert to Range


Regards
Claus B.

Hi Claus, yes, its the same on English version, but the macro recorder does not record that action, strange I think.

Howard

I don't know exactly how it is named in the english Version. But
try: Right clikc to column A = Table = Convert to Range


Regards
Claus B.

Hi Claus, yes, its the same on English version, but the macro
recorder does not record that action, strange I think.

Howard

That's because most (if not all) of the Table object is not exposed to
VBA, thus the problem with coding it.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Fri, 27 Nov 2015 01:43:23 -0800 (PST) schrieb L. Howard:

Hi Claus, yes, its the same on English version, but the macro recorder does not record that action, strange I think.

do it this way:

Dim i As Integer
With Sheets("Summary")
For i = 1 To .ListObjects.Count
.ListObjects(1).Unlist
Next
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 27 Nov 2015 10:51:13 +0100 schrieb Claus Busch:

sorry typo:

.ListObjects(1).Unlist
.ListObjects(i).Unlist


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Yes, that's one way to access a Table object by code...

.ListObjects(1).Unlist

...but I think you meant...

.ListObjects(i).Unlist

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

do it this way:

Dim i As Integer
With Sheets("Summary")
For i = 1 To .ListObjects.Count
.ListObjects(1).Unlist
Next
End With


Regards
Claus B.

Hi Claus,

Thanks, got it, (typo noted).

Thanks,
Howard

Hi all,

The sheets are single letter A to G on sample workbook, A to Z in
real workbook.

I did not submit the code: For i = Asc("A") To Asc("G").

Another question, please.

Summary sheet has a Table, which seems to me to be column A. I can
go to name box and Table1 is listed.

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

Are there common rules on how to copy to a table as we are here. I
anticipate some problems here. If you delete the data in the
table/filtered area, the next copy goes to the first empty row below
the table. That could be 50 + rows down.

If you delete all the data filled ROWS of the table, then you have
the Headers and a empty 1st row of the table, and the copy goes to
the first row below that empty table row.

Perhaps, the table and filter should be "Turned Off" and old data
deleted, to leave a row of Headers, then do the copy, then reinstate
the table and filter???

Howard

The *Table object* is problematic for coding (IMO) because this object
has its own rules, and is primarily used for data analysis scenarios,
not for storing raw data. You have to delete the table to remove it.

When writing new data to a summary sheet, filters should be turned off
so inbound data is correctly positioned. Filtering shouldn't really
matter since the rows are contiguous in col1 (or should be) since the
1st col usually contains the *PrimaryKey* in database tables. (I think
it's a good practice to follow the rules<g)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi all,

The sheets are single letter A to G on sample workbook, A to Z in
real workbook.

I did not submit the code: For i = Asc("A") To Asc("G").

Another question, please.

Summary sheet has a Table, which seems to me to be column A. I can
go to name box and Table1 is listed.

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

Are there common rules on how to copy to a table as we are here. I
anticipate some problems here. If you delete the data in the
table/filtered area, the next copy goes to the first empty row
below the table. That could be 50 + rows down.

If you delete all the data filled ROWS of the table, then you have
the Headers and a empty 1st row of the table, and the copy goes to
the first row below that empty table row.

Perhaps, the table and filter should be "Turned Off" and old data
deleted, to leave a row of Headers, then do the copy, then
reinstate the table and filter???
Howard

The *Table object* is problematic for coding (IMO) because this
object has its own rules, and is primarily used for data analysis
scenarios, not for storing raw data. You have to delete the table to
remove it.

When writing new data to a summary sheet, filters should be turned
off so inbound data is correctly positioned. Filtering shouldn't
really matter since the rows are contiguous in col1 (or should be)
since the 1st col usually contains the *PrimaryKey* in database
tables. (I think it's a good practice to follow the rules<g)

Claus' suggestion is, as I recall, the way to delete a table object so
it's just a range table of data.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

The *Table object* is problematic for coding (IMO) because this object
has its own rules, and is primarily used for data analysis scenarios,
not for storing raw data. You have to delete the table to remove it.

When writing new data to a summary sheet, filters should be turned off
so inbound data is correctly positioned. Filtering shouldn't really
matter since the rows are contiguous in col1 (or should be) since the
1st col usually contains the *PrimaryKey* in database tables. (I think
it's a good practice to follow the rules<g)

--
Garry


Hi Garry,

This gives me sound advice to pass on if the OP has trouble copying to his table. Basically, turn off the filter, and copy to a range NOT a table.

Reinstate filters/tables as needed with the new data.

Thanks

Howard

The *Table object* is problematic for coding (IMO) because this
object has its own rules, and is primarily used for data analysis
scenarios, not for storing raw data. You have to delete the table
to remove it.

When writing new data to a summary sheet, filters should be turned
off so inbound data is correctly positioned. Filtering shouldn't
really matter since the rows are contiguous in col1 (or should be)
since the 1st col usually contains the *PrimaryKey* in database
tables. (I think it's a good practice to follow the rules<g)

--
Garry


Hi Garry,

This gives me sound advice to pass on if the OP has trouble copying
to his table. Basically, turn off the filter, and copy to a range
NOT a table.

Reinstate filters/tables as needed with the new data.

Thanks

Howard

It could also be that Table1 is just a DefinedName given to colA, so
look there before drawing any conclusions...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion