Hi,
Yes you could easily do something to identify that condition.
Add the new column A as I suggested, so that now you're trying to
check Columns B to T
Now in A1 have the macro enter
=IF(COUNTA(B1:T1)<>19,"Keep","Delete")
and copy down column A as appropriate
Provided every cell in a row contains an entry, then CountA will
evaluate to 19 and the IF will return the value "Delete"
You can now filter on Column A and delete all the relevant rows with
something like
Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).EntireRow.Delete
I'd advise also first creating an additional row 1 with the word
"Keep" in A1 so that when you filter, the filter doesn't start with
the first row of data in case this should happen to be a "Keep" row.
That's because the filter headings remain visible and will be selected
by the currentregion command and deleted.
HTH
On 14 Sep 2006 10:57:02 -0700, "indraneel" <(E-Mail Removed)>
wrote:
>hi Richard
>
>i got your point but cant think of a formula because out of the lines i
>have to delete there are some that have a entry in each and every cell
>can i say something like if there is an entry in each cell from a to s
>delete
>
>but again i have opthers that have only 1 or 2 cells filled which i
>want to delete
>
>help!!!!
>
>
>regards
>indraneel
>
>Richard Buttrey wrote:
>> Hi,
>>
>> No, making r2 relative won't make any difference since the problem is
>> that you are deleting the r2 range completely.
>>
>> I don't fully understand your data and where the lines you want to
>> delete appear, but as a minimum you'll need to re Set range r2 each
>> time you go through the loop. Hence the
>>
>> Set r2 = ActiveSheet.Rows("46:52")
>>
>> needs to be inside the loop, but of course you'll need to work out and
>> build in which rows are relevant each time, since 46:52 each time
>> apart from the first pass, will no doubt be incorrect.
>>
>> I have a lot of similar macros, where a .prn or .txt file that's
>> imported from another application, needs to have all sorts of banner
>> headings, column headings, footers and surplus format lines, deleted
>> to end up with a neat flat database.
>>
>> After adopting several methids, the approach I've found best is as
>> follows.
>>
>> 1. Import the data
>> 2. Add a helper column A
>> 3. Work out an Excel formula that will identify whether a particular
>> row is one you want to keep or not. e.g in A1
>>
>> =If(and("B1"<>"", F1<>"----")."Keep","Delete")
>>
>> and have the macro put this in A1
>>
>> 4. Now copy this formula down column A for the whole database
>> 5. Filter the database on column A for the word "Delete")
>> 6. Delete all the filtered rows
>> 7 Finally unfilter the database, delete column A and Bob's your Uncle
>> (or whatever is your local equivalent of Job done!)
>>
>> HTH
>>
>>
>>
>>
>> On 14 Sep 2006 10:15:04 -0700, "indraneel" <(E-Mail Removed)>
>> wrote:
>>
>> >hello Richard
>> >
>> >i have data such as
>> >
>> >Element ID SMAx Smin etc etc (All these being headers )
>> >
>> >Then I have data
>> >I get my analysis output in a textpad file from where i import it
>> >It imports the Headers at every page end
>> >This is what i want to delete
>> >and make the data a continuous one
>> >I want to do a DO LOOP because i have say atleast 10 (Textpad pages) of
>> >data per run
>> >
>> >And Yes I had commeneted out the selection.delete while testing
>> >
>> >Thank you for the help
>> >
>> >and i thought on th same lines that the program once it deletes the
>> >lines doesnot recognize the r2 set
>> >a question will it help if i say r2 as a relative reference (dont think
>> >that is going to help)
>> >
>> >
>> >
>> >Thank you and Regards
>> >Indraneel
>> >Richard Buttrey wrote:
>> >> I assume you've commented out the Selection.delete whilst testing it.
>> >>
>> >> The reason you get the error message is that once you delete the range
>> >> r2, that's it. VBA no longer has a range r2 anymore, hence next time
>> >> round the loop when it tries to select r2, it complains.
>> >>
>> >> If you explain in a little more deatil what you're trying to achieve,
>> >> no doubt we can help.
>> >>
>> >> Rgds
>> >>
>> >>
>> >> On 14 Sep 2006 07:50:35 -0700, "indraneel" <(E-Mail Removed)>
>> >> wrote:
>> >>
>> >> >hello
>> >> >i need some help with deleting Rows in excel using a do loop
>> >> >
>> >> >my program looks like this
>> >> >Sub Cleanup()
>> >> >Dim r1 As Range
>> >> >Dim r2 As Range
>> >> >
>> >> >Set r1 = Cells(6, 2)
>> >> >Set r2 = ActiveSheet.Rows("46:52")
>> >> >
>> >> > Do While r1.Value <> ""
>> >> > r2.Select
>> >> >'Selection.Delete Shift:=xlUp
>> >> >
>> >> >r2.Font.Bold = True
>> >> >Set r1 = r1.Offset(47, 0)
>> >> >Set r2 = r2.Offset(47, 0)
>> >> >
>> >> >Loop
>> >> >
>> >> >End Sub
>> >> >
>> >> >
>> >> >The problem i am having is when i make the things i want to delete bold
>> >> >they do become bold but when i want to delete those rows it does for
>> >> >the first row and then stops and says "OBJECT REQUIRED" i known i make
>> >> >making a mistake in the code somewhere but not getting where
>> >> >
>> >> >
>> >> >thank you in advance for your help/advice
>> >> >
>> >> >
>> >> >Regards
>> >> >Indraneel
>> >>
>> >> __
>> >> Richard Buttrey
>> >> Grappenhall, Cheshire, UK
>> >> __________________________
>>
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
|
Similar Threads
