Is there more than 1 row selected?

I need to stop a procedure if more than one row is selected.  However if there is a way to run the same procedure on multiple rows I am up for that... so it will run not matter how many rows are selected.  It just needs to end with that same number of rows selected prior to Ln9 running.

Sub ReRunLnZ()
   Dim rng
    Range("B" & (ActiveCell.Row)).Select  'select cell B of active row.
    
   'Add something to select cell B of however many rows are selected ...

    Set rng = Range(ActiveCell, ActiveCell.Offset(0, 5000))  'Select a lot to the right
    Range(Selection, rng).Select
    Selection.ClearContents

'  Reselect the rows column A that were cleared,  only Column A will have data in it.

    ActiveCell.Offset(0, -1).Select
    Application.Run "SIF_Creator.xlsm!GetMatlNumberFromCatCode"
End Sub

Open in new window

RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If rng.Rows.Count > 1 Then
gowflowCommented:
Can you simply tell us what you want to achieve ? instead of showing a code that we have to fiddle with ?
gowflow
Martin LissOlder than dirtCommented:
Sub ReRunLnZ()
   Dim rng
    Range("B" & (ActiveCell.Row)).Select  'select cell B of active row.
   
   'Add something to select cell B of however many rows are selected ...

    Set rng = Range(ActiveCell, ActiveCell.Offset(0, 5000))  'Select a lot to the right
    If rng.Rows.Count > 1 Then Exit Sub
    Range(Selection, rng).Select
    Selection.ClearContents

'  Reselect the rows column A that were cleared,  only Column A will have data in it.

    ActiveCell.Offset(0, -1).Select
    Application.Run "SIF_Creator.xlsm!GetMatlNumberFromCatCode"
End Sub
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

RWayneHAuthor Commented:
So in English

know matter what rows are selected, select cell B, of those rows, clear them from column B, to the right however many columns that data goes out.  Then end it by selecting Col A, of the original cells selected.

Hope that makes sense.  I do not care if one is selected or not at this point, it was only written to do one line at a time and I would like it to work for however many rows are selected.
RWayneHAuthor Commented:
what I meant to say is to clear them including cell B.
gowflowCommented:
Let me rephrase what you said:

if rows selected are 14 to 17 (entire rows) then you want:

B14 to B17 to their extreme right say until col O there is data then it is B14:O17 to be cleared from data and you want the cursor to select A14.

and if say M20 is selected then you want B20 to extreme right say again O then it is clear B20 to O20 cleared from data and A20 is selected

is my interpretation correct ?
gowlfow
RWayneHAuthor Commented:
Sort of...  not entire rows..  if cells selected...  like H14 to H17.  Clear everything from B14 to B17 out to the right... like column BL (goes way out.).  After clear. Leave it then with A14 to A17 selected.

Of course this is an absolute example and I am looking for a dynamic one so whatever rows are selected to start, the same thing will happen for them as well.
gowflowCommented:
ok noted and sorry for the delay due to timezone difference.

Here is the Main code for that (you create a Module and insert this code into the module like in the attached workbook. I have added a button so the way it works you select whatever part you want single cell or multiple whatever it is and then press on the button and see the results.

Here is the code

Sub SelectionClear(Target As Range)
Dim WS As Worksheet
Dim TopRow As Long, BotRow As Long, MaxCol As Long, I As Long, TmpCol As Long


Set WS = ActiveSheet
TopRow = Target.Row
BotRow = TopRow + Target.Rows.Count - 1
MaxCol = 2

For I = TopRow To BotRow
    TmpCol = WS.Cells(I, WS.Columns.Count).End(xlToLeft).Column
    If TmpCol > MaxCol Then MaxCol = TmpCol
Next I
WS.Range(WS.Cells(TopRow, "B"), WS.Cells(BotRow, MaxCol)).ClearContents
WS.Range(WS.Cells(TopRow, "A"), WS.Cells(BotRow, "A")).Select

End Sub

Open in new window


Let me know.
gowflow
SelectionClear.xlsm
RWayneHAuthor Commented:
This looks like it,  Need some time to test.  Thanks.
RWayneHAuthor Commented:
Ok...  Your sample workbook works fine.... however when I use this my procedure it fails it define:
TopRow = Target.Row
BotRow = TopRow + Target.Rows.Count - 1

Open in new window

 

Why does this work in your sample file and not when I pull it into another procedure?  What would stop TopRow from getting a value.  I am assuming the BotRow is not getting defined either?  Does not make sense.  Working on mocking up a sample file.
RWayneHAuthor Commented:
In the sample file, we should be able to selection two rows of data, and leave column A with data in it.  Select some rows and run Sub FetMatlNumberFromCatCode

See how it is stopping.  Any ideas?
SIF_Creator2.xlsm
RWayneHAuthor Commented:
I see that it is using a CommandButton?  Is there a way to use a Form Control instead of and ActiveX Control?  Is that why it is not working for me?
gowflowCommented:
ok again delay due to timezone difference.

I created a Form button for you called Clear Before Surf (same Module as you created and put there a pres sub that will capture the selection you have made and with it, it will call the procedure SelectionClear).

Check it and let me know if this is what your looking for.

gowflow
SIF_Creator2-V01.xlsm
RWayneHAuthor Commented:
Ok.. look like it is working...  how come when I add it to another procedure using Call ClearBeforeSurf it returns a Compile Error:  Expected variable or procedure, not module.  In most others calling the procedure would work just fine.  Any ideas?
RWayneHAuthor Commented:
Also if I use Call SelectionClear it errors to with:  Compile error:  Argument not optional.  I am trying to run this at the very beginning of mod  GetMatlNumberFromCatCode()
RWayneHAuthor Commented:
Instead of running them as two separate buttons, one to clear and another the gather data, because are after the area is cleared I run GetMatlNumberFromCatCode()
RWayneHAuthor Commented:
Minor request to add?  After the clear, is there a way to remove any background colors?  While I gather data I color some rows when errors are found in SAP.  When clearing it would be nice to have them back to a truly cleared state..   Would really appreciate that add.
gowflowCommented:
ok but sake productive feedback please refrain from having several posts following each others but concentrate all your questions in 1 post so I can address all of them and try numbering the questions. I will try to answer your questions:

1) how come when I add it to another procedure using Call ClearBeforeSurf it returns a Compile Error:
Sorry my mistake it seems Excel doesn't like same name for procedure and Module so in this version I changed it to ClearBeforeSurfSub now you can call it from anywhere else.

2) Also if I use Call SelectionClear it errors to with:  Compile error:
Obviously it should have the argument Selection with it as it will start with a Selection and just calling it by itself will not work. You are betteroff always calling ClearBeforeSurfSub (for sure you have to make sure that you have previously made your selection or else you are defeating the whole purpose of the subroutine.

3)Instead of running them as two separate buttons, one to clear and another the gather data, because are after the area is cleared I run GetMatlNumberFromCatCode()

I noticed that you have dumped the whole routine there and this is not recommended !!! You only need to remove all the code that you added and just put this line at the beginning of GetMatlNumberFromCatCode()
ClearBeforeSurfSub
I will make the amendment in this attached file if you don't like it please let me know.

4) Minor request to add?  After the clear, is there a way to remove any background colors?
Has been included in this version. You mentioned 'Colors' so I only removed Colors and Comments if any but if you have text that is bold or underline or italic of you have cell formatting like $ or Dates it will all remain.

Please check and let me know your comments.
gowflow
SIF_Creator2-V02.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
Sorry, as I was thinking of things I just posted, next time I will edit the post... and add.

It looks like this will work.  The only thing is when it clears the color it is only clearing it on Col B,  I was hoping that it could clear it way off to the right on cell out there too?  No need to clear color or comment from Col A.  That would be awesome..
gowflowCommented:
Say this again ???

We are clearing data from Col B and off to maximum right same as we are doing for colors !!!
We are not clearing colors from Col A !!!

check your end again
gowflow
RWayneHAuthor Commented:
Excellent!!!!   Thanks you so much...  the colors not clearing is an on/off thing and I have not pinned down why it is on some and not on others.  We can live with the colors, it actual can flag the one that failed and then were fixed.  Appreciate the help.  I would give you 2500 pts as there are about 5 questions mix in the thread.
gowflowCommented:
No problem your welcome.

I will be happy if you can post a new question where we can tackle the colors but you will need to give me an example and tell me what is not working and will be glad to assist. Never leave any issue unresolved !!!

If you decide to go along with this path please post here a link to the new question and will be glad to assist.

Regards
gowflow
RWayneHAuthor Commented:
It was actually better to leave the color there... it flagged the number of rows that had to be fixed, so it ended up helping to keep it there.  Thanks for the extended offer to assist.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.