delete alot of rows with a specific value from a table

How do I delete alot of rows with a specific value from a table in excel ?
See attached.
How do I delete all the rows with the value 2 in column C in this table, all in one go??
I can get this to work for a non table see Sheet1, but it seems to do more deleting when it is in a Table format.

Please let me know if I need to clarify further.
Tks

As Range B2 to D24 is set up as a "Table" Excel imposes certain restrictions of what one can and can't do in order to preserve the table format. To overcome these restrictions click on B2 then select "Table Tools" tab and click on "Convert to Range" and answer "Yes".

Now you can edit the range to your hearts content. After editing you can then select the range and convert it back to a table.

Alf

Thanks Alf that does the trick.
Just out of curiosity, before I do the "Convert to Range" the Table will be called, lets say "Table1", but then after I do my filtering and deleting, I then convert the data back to a Table what should it be called? I think it will still be called "Table1". but any how this can be changed in the formulas. The reason I ask is that I have pivot tables being fed of this original Table.

And just for my reference I select B2(this will show Table Tools) - select Design - "Convert to Range" - select yes

Thanks again!!

As I've not worked that much with tables before I found it interesting to try and write a macro that

1) Converts a table to a range

2) Filters the range to find all values equal to 2 in column C

3) Deletes these rows

4) Converts the rest of the data back to a table names "Table1"

To test run macro "FixRng"

Alf

You need to delete row 1 as it not part of the table.

You need to delete row 1 as it not part of the table.

?????

Alf

Alf,
I was referring to OP's sheet, not your sheet. I kept getting an error until I deleted the empty row 1, so I guess it is probably that row is not part of the table.

My code filters the table, not the sheet.

Hey Alf, that's what I was about to start doing. but it would not have been as good as that I don't think.

Can you please help me understand it better

Please Login or Register  to view this content.

1 - Is ListObjects(1) the Talble in question that you are Unlisting?
2 - What is .UsedRange?
3 - why the offset(1) here?
4 - what does the CurrentRegion do here?
Also can your explain these more: xlSrcRange, rng, , xlYes?

Tks

The line

Please Login or Register  to view this content.

converts the list object "Table1" to a range. If you had 2 or more tables in a sheet you could convert a specific table to a range by using the "ListObject" number

The

Please Login or Register  to view this content.

is a term defined by Excel and it is of course the "used range" in a sheet. If you have a range B2 to H10 with data the command "used range" will select this area.

The downside of this command is that if you had some comments or a value in a cell says M3 then this command will select the range B2 to M10 as the "used range"

The "CurrentRegion" is a bit different in its behaviour. First you must select topmost left cell so using the example above the command would be

Please Login or Register  to view this content.

and this will select range B2:H10 as "current region" expects data to be coherent. If there is a blank row or a blank column this will stop the selection of what is the "current region". So in this case the data in M3 will be ignored when "CurrenRegion" is "selected". For working with tables "CurrenRegion" is rather useful as you can mix tables and comments knowing that comments will be ignored as long as there is a blank line or column between your table and comments.

As the "UsedRange" command after filtering will contain both the headings and the filtered values and I don't wish to delete the headings I use the offset command to delete all rows starting one row below. The offset command contains the row offset and column offset. Used the way I did it is "translated" as a row offset.


For your last question see Microsoft link
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Alf

Tks Alf

my issue was mainly with the "CurrentRegion" and ".UsedRange" and I kind of get it now...

I was trying to apply you macro to my specific table I have but I had randome data in cell B1 and A8 for example which made your macro not work for me. So I just had to delete the contents of these cells for it to work. This helps me understand "CurrentRegion" and ".UsedRange" a bit better. But I suppose my specific question is why use CurrentRegion" 1 time and ".UsedRange" another in your original macro.

Anyhow, all good and thanks again you have been very helpful!!

But I suppose my specific question is why use CurrentRegion" 1 time and ".UsedRange" another in your original macro.

Just a whim as I usually like to vary the commands I use when writing macros.

Yes I see your problem and as you works with tables and would like to add some text as well "CurrentRegion" should be the command that suits you best.

Lookin at your uploaded example you could have text/data in column A and text/data in row 1 and if you move your table so it starts in cell C3 then you could then use "currentRegion" without any problem i.e.

Please Login or Register  to view this content.

Hopefully this solves you problem. If so could you please mark your thread "Solved".

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

tks alf. solved!!