Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
Ive got these few lines of code that work fine on for a group of cells Im
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 Im 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? -- Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
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 "Al" wrote: Ive got these few lines of code that work fine on for a group of cells Im 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 Im 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? -- Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
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 "FSt1" wrote: 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 "Al" wrote: Ive got these few lines of code that work fine on for a group of cells Im 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 Im 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? -- Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
Hello Don and thanks for the offer.
Another responder provided theinfo I needed.-- Thank you "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Ive got these few lines of code that work fine on for a group of cells Im 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 Im 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? -- Thank you . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
Your code is VERY inefficient to the point where I didn't know what you were
doing. Hence,my offer. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Hello Don and thanks for the offer. Another responder provided theinfo I needed.-- Thank you "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Ive got these few lines of code that work fine on for a group of cells Im 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 Im 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? -- Thank you . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
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 Id 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. -- Thank you "Don Guillett" wrote: Your code is VERY inefficient to the point where I didn't know what you were doing. Hence,my offer. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Hello Don and thanks for the offer. Another responder provided theinfo I needed.-- Thank you "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Ive got these few lines of code that work fine on for a group of cells Im 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 Im 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? -- Thank you . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable counting of rows
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... 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 Id 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. -- Thank you "Don Guillett" wrote: Your code is VERY inefficient to the point where I didn't know what you were doing. Hence,my offer. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Hello Don and thanks for the offer. Another responder provided theinfo I needed.-- Thank you "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Al" wrote in message ... Ive got these few lines of code that work fine on for a group of cells Im 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 Im 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? -- Thank you . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting variable ranges and auto-summing variable ranges | Excel Programming | |||
Counting Rows Then Counting Values in Columns | Excel Programming | |||
Counting Variable Length of Rows in a Named Range | Excel Programming | |||
Counting Variable Length of Rows in a Named Range | Excel Programming | |||
Counting Variable Length of Rows in a Named Range | Excel Programming |