If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Deleting Certain Data in Excell
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Deleting Certain Data in Excell

#1
June 25th 12, 12:33 PM
 Kathryn39 Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 5
Deleting Certain Data in Excell

Hello,
I have three columns of data, vertex points for x y and z coordinates, with a total of 65 thousand rows.

In my A1 column the numbers repeat itself; they run from 0 to 255, 255 times. I'm trying to delete rows that run from 0 to 99 and just be left with the values 100 to 255, and also to have no gaps left from the deleted fields.

Is there a quick way of doing this? All advice appreciated!

Thanks,
Kathryn
#2
June 25th 12, 02:26 PM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 658

Quote:
 Originally Posted by Kathryn39 Hello, I have three columns of data, vertex points for x y and z coordinates, with a total of 65 thousand rows. In my A1 column the numbers repeat itself; they run from 0 to 255, 255 times. I'm trying to delete rows that run from 0 to 99 and just be left with the values 100 to 255, and also to have no gaps left from the deleted fields. Is there a quick way of doing this? All advice appreciated! Thanks, Kathryn

Hi Kathryn,

One way would be to enter the below foruma into row two of a blank column and copy down to the last populated row. Then filter that column to show only "X", then highlight and delete those rows.

=IF(AND(A2>=0,A2<=99),"X","")
#3
June 25th 12, 02:29 PM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 658

Quote:
 Originally Posted by Kathryn39 Hello, I have three columns of data, vertex points for x y and z coordinates, with a total of 65 thousand rows. In my A1 column the numbers repeat itself; they run from 0 to 255, 255 times. I'm trying to delete rows that run from 0 to 99 and just be left with the values 100 to 255, and also to have no gaps left from the deleted fields. Is there a quick way of doing this? All advice appreciated! Thanks, Kathryn
You could also filter column A to be greater or equal to 0 and less than or equal to 99 and then delete these rows...
#4
June 25th 12, 03:46 PM
 Kathryn39 Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 5

Hi Spencer,

thanks a million that worked, great help cheers!

Kathryn

Quote:
 Originally Posted by Spencer101 Hi Kathryn, One way would be to enter the below foruma into row two of a blank column and copy down to the last populated row. Then filter that column to show only "X", then highlight and delete those rows. =IF(AND(A2>=0,A2<=99),"X","")
#5
June 25th 12, 06:16 PM posted to microsoft.public.excel.misc
 Ron Rosenfeld[_2_] external usenet poster Posts: 901
Deleting Certain Data in Excell

On Mon, 25 Jun 2012 11:33:12 +0000, Kathryn39 > wrote:

>
>Hello,
>I have three columns of data, vertex points for x y and z coordinates,
>with a total of 65 thousand rows.
>
>In my A1 column the numbers repeat itself; they run from 0 to 255, 255
>times. I'm trying to delete rows that run from 0 to 99 and just be left
>with the values 100 to 255, and also to have no gaps left from the
>deleted fields.
>
>Is there a quick way of doing this? All advice appreciated!
>
>Thanks,
>Kathryn

I believe you can use the AutoFilter method, if you don't mind deleting the entire row:

This assumes labels in row 1
Autofilter by column A by using a Number Filter; less than or equal to 99
select the range that is visible, EXCLUDING Row 1
Edit / Find & Select / GoToSpecial / Visible Cells Only
Cells / Delete : You will see a dialog box asking if you want to delete the entire row; answer Yes.

If this is something you will need to do many times, it can be automated using a macro.

If you cannot delete the entire row, post back and we can do an area by area deletion.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Excell Data Filter Fails to Integrate New Data-rows Richard-44 Excel Discussion (Misc queries) 2 November 10th 07 03:30 AM Excel Macro for taking data external data and populating it on a sheet and deleting unwanted data [email protected] Excel Programming 3 November 8th 07 05:59 AM Deleting cell data without deleting formula Tom Hall Excel Discussion (Misc queries) 4 October 29th 06 04:07 PM Deleting nonactive Sheets in Excell Tom Ogilvy Excel Programming 0 August 31st 04 02:37 PM Deleting nonactive Sheets in Excell Bernie Deitrick Excel Programming 0 August 31st 04 02:31 PM

All times are GMT +1. The time now is 04:19 PM.