Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 4th 06, 02:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 1
Default Help required on a formula

I import a large file which may have a few hundred rows.
in the rows there are the following
Zero value cell, Blank cell and numerical cells
I need to remove the cells that have a Zero value cell or are Blank.
Only the numerical cell need to remain

The starting cell is E7

So if E7 has a numerical value. move to E8 if numerical move to E9 etc.
if however there is a blank space or a zero the row needs to be
deleted.

Your help greatly appriciated

Thanks Gavin


  #2   Report Post  
Old September 4th 06, 03:07 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 44
Default Help required on a formula


Sub Clrdata()
For i = 0 To 2000 '2000 if you have 2000 rows, 3000 for 3000 rows etc
Sheets("Sheet1").Select 'Sheet with info on
Cells(7 + i, 5).Select 'row 7, column E=5
If ActiveCell = "" Then Rows(7 + i).Delete Shift:=xlUp
If ActiveCell = "0" Then Rows(7 + i).Delete Shift:=xlUp
Next
End Sub
--
Dave


"Gavin" wrote:

I import a large file which may have a few hundred rows.
in the rows there are the following
Zero value cell, Blank cell and numerical cells
I need to remove the cells that have a Zero value cell or are Blank.
Only the numerical cell need to remain

The starting cell is E7

So if E7 has a numerical value. move to E8 if numerical move to E9 etc.
if however there is a blank space or a zero the row needs to be
deleted.

Your help greatly appriciated

Thanks Gavin


  #3   Report Post  
Old September 4th 06, 03:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 19
Default Help required on a formula

If you don't need to delete the rows you could try to 'autofilter' the list.

Then do a custom filter on the lines of ' is greater than' 0.



"Gavin" wrote:

I import a large file which may have a few hundred rows.
in the rows there are the following
Zero value cell, Blank cell and numerical cells
I need to remove the cells that have a Zero value cell or are Blank.
Only the numerical cell need to remain

The starting cell is E7

So if E7 has a numerical value. move to E8 if numerical move to E9 etc.
if however there is a blank space or a zero the row needs to be
deleted.

Your help greatly appriciated

Thanks Gavin




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula required Sohail khan Excel Discussion (Misc queries) 2 March 20th 10 07:49 AM
formula required Sohail khan New Users to Excel 0 March 19th 10 07:40 PM
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible blue[_2_] Excel Discussion (Misc queries) 2 July 11th 07 06:08 PM
Formula required shaji Excel Discussion (Misc queries) 3 February 19th 07 01:56 PM
Help Required With IF AND Formula Ian Excel Worksheet Functions 3 February 25th 05 12:30 PM


All times are GMT +1. The time now is 09:12 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017