Variable counting of rows

[Al]

I’ve got these few lines of code that work fine on for a group of cells I’m
calling “Large†that starts on row 5:

Columns("A:A").Select
Selection.Find(What:="Large", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Range("A1").Select
NumRows = Range("B5", Range("B5").End(xlDown)).Rows.Count
For x = 1 To NumRows
If ActiveCell.Offset(0, -1) = "SEL" Or ActiveCell.Offset(0, -1) = "Asset
CL" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Range("A1:S1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 1).Select
End If
Next

I’m looking for a modification for the next group of cells.

How might I re-define “NumRows†to handle all my cell groups that start on
rows further down my sheet?

 

[FSt1]

hi
why did you "find" large when you knew it started at B5?
how long is the list?
how will you find your other groups of cells further down?
is your list broken up with blanks in it?
if you can find the starting point of your groups of cells you might use this
numrows=range(activecell, activecell.end(xldown)).rows.count
but i see nothing in your code to find the next group of cells.
regards
FSt1

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Al]

Your defintion for NumRows solved my problem.
As to your other questions, I didn't include the full macro as other parts
of code accounted for those issues. I can send the full code & smaple
spreadsheet if you would find it beneficial for you.--
Thank you

 

[Al]

Hello Don and thanks for the offer.
Another responder provided theinfo I needed.--
Thank you

 

[Don Guillett]

Your code is VERY inefficient to the point where I didn't know what you were
doing. Hence,my offer.

 

[Al]

Dear Don,

I accept your comment because I am in not position to disagree as it was I
who went to the forum looking for help because of my lack of knowledge.

In the same vane I thanked you for your generous offer of assistance in my
original reply.

I can certainly appreciate your dilemma. In my own field of expertise (which
obviously is not Excel programming) I too sometimes have difficulty answering
questions from those whose level of achievement is still beneath mine.

But just like the forum I do try an assist and I am thankful that the forum
functions (ha, a little Excel pun) because of individuals like yourself who
offer their time and expertise to others.

I also try and learn from the solutions I receive in the forum. For example
in this case I learned how to reference an ActiveCell in a range.

I could certainly send you my coding and without a doubt I do not disagree
that it could be re-written to be more efficient and certainly more elegant.
But as the saying goes, we all must learn how to crawl before we can walk.

My own dilemma is that while I’d certainly like to become more proficient in
my coding that effort is superseded by time available and the more immediate
concern of how to use Excel as a tool to solve the problem at hand. But I
will still continue to learn thanks to your efforts and others like you on
the forum.

In lieu of sending you my entire file with before and after results might I
ask a different favor?

How might I make my questions more clear? In some of my posts, like this
one, I either seem to frustrate the respondent or get back much more
information than I actually wanted to the point that the solution is not
worth the time to code.

(I mean no disrespect to you or other respondents but most of the time my
purpose in writing a macro is to make a particular problem more efficient to
solve; both this time and in the future. By that criterion and with my level
of knowledge I need to determine if the effort to correctly code the solution
is more efficient than a manual solution.)

I made a special effort in my original post to point to what I thought was
the solution I was seeking; how might I re-define NumRows?

Obviously this did not work for you and it also seemed to concern the other
respondent. My thought was not to complicate my question by providing more
information than necessary for the solution and thereby not waste the time of
someone who has generously offered to assist by writing more info than
necessary to address the question.

But again I ask how could I have been more clear or direct so that I can
write better posts so that I can continue to learn for those like you?

I will also re-read the forum posting guidelines to possibly learn from my
mistake.

 

[Don Guillett]

My point in seeing your file with all of your code was meant to try to help
you learn to write more efficient code. For instance, selecting is almost
never desirable or necessary.

Sheets("sourcesheet").select
range("a2:a22").select
selection.copy
Sheets("destsheet").select
range("a2").select
selection.paste

can be reduced to ONE line and speed up the execution of the code and can be
run from anywhere in the workbook.

Sheets("sourcesheet").range("a2:a22").copy Sheets("destsheet").range("a2")
I repeat my offer of assistance.