MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Newbie needs help

  Asked By: Richard    Date: Dec 21    Category: MS Office    Views: 1242

Looking for code in VBA that will help me edit an imported file to
delete certain rows. Need to be able to check the cell for certain data
and delete rows not needed. I can't get some modified code to work that
I use to hide some rows in another spreadsheet. There are blank rows,
and two rows with data one to keep and the other to delete. The one to
keep has 23 blank spaces before a word that I can use to compare to.
Attempting to use the Do While not equal to a cell at the bottom I put
something in to stop the Do While routine. Anyone have any code or where
I can find some I can modify?



1 Answer Found

Answer #1    Answered By: Aaleyah Khan     Answered On: Dec 21

I'll give it a crack. I'll need to make a few assumptions,
which I'll describe below.

First, the assumptions:

1. you have 10 columns of data
2. your data  to be checked is always in col E .
3. by "delete", you mean you want that row made blank.

Next, the code:

01.. rrbeg = 10
02.. rrend = 500
03.. cc = 5
04.. keyword = "keep me"
05.. For rr = rrbeg to rrend
06..... txt = Cells(rr, cc)
07..... chk = LTrim(txt)
08..... If chk <> keyword Then
09......... For jj = 1 to 10
10............. Cells(rr, jj) = Empty
11......... Next jj
12..... End IF
13.. Next rr

If my assumptions are correct, then here's a quick
description of the code.

Lines 1 and 2 set the beginning and ending row numbers
where data exists.

Line 3 sets key column to col 5 (which is col "E").

Line 4 establishes the key word.

Line 5 is the outer loop. I'm not a big fan of Do While,
so I've taken the liberty to change the loop construct
to a For..Next type. Feel free to do it your way if
you prefer.

Line 6 extracts the contents of col E of the current row
and puts it in variable txt.

Line 7 performs a simple Left Trim. This is to deal with
your 23 blank  spaces issue. If the number 23 is vital, then
you could simply change Line 4 to include 23 blanks, and
then eliminate this line.

Line 8 is the compare  branch. If the comparison fails
the branch is entered to perform the delete.

Lines 9 to 11 "delete" the current row -- by setting the
cells in columns A thru J to Empty.

That should do it.
Hope this is close to what you are looking for.
Holler if it isn't.

Didn't find what you were looking for? Find more on Newbie needs help Or get search suggestion and latest updates.