Solved

Naming a range based on uniqueness of another column

Posted on 2016-08-10
11
35 Views
Last Modified: 2016-08-11
The attached sheet has a key in column A, it defines how many are in a range.  Using ActiveCell.  (assuming the active cell is the first record in a bunch, and I click on it.)  So I would like to define a range in column B and column C.  So if I place my cursor in A2, it would identify B2 thru B7 and name it "Qtys"  and C2 thru C7 as "CatCode"

I would then like to goto sheet2 and be able it paste in  Qtys and Catcode.

Is there a way to just grab one row, 4 rows or 6 rows as ranges to use when pasting it into another sheet?
GroupingBasedOnAnotherCol.xlsx
0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41751365
You can add a header line and do filtering, like I show in the attached version of your workbook
GroupingBasedOnAnotherCol.xlsx
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41751517
Please try this....


Sub CopyPasteAsPerSelectionInColumnA()
Dim sws As Worksheet, dws As Worksheet
Dim sRow As Long, eRow As Long

Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")

If Selection.Parent.Name <> sws.Name Then
    MsgBox "Please select the cell on " & sws.Name & " and then try again...", vbExclamation, "Wrong Sheet Selected!"
    Exit Sub
End If
If Selection.Column <> 1 Then
    MsgBox "Please select a cell in column A and then try again...", vbExclamation, "Wrong Selection!"
    Exit Sub
End If

sRow = sws.Range("A:A").Find(what:=ActiveCell.Value).Row
eRow = sws.Range("A:A").Find(what:=ActiveCell.Value, searchdirection:=xlPrevious).Row
dws.Cells.Clear
sws.Range("B" & sRow & ":C" & eRow).Copy dws.Range("A1")
dws.Columns.AutoFit
dws.Activate
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 41751537
I don't believe the question mentions filtering in fact the object to this is not to filter.   Did not see in the attached named ranges of  Qty or CatCode? That's why I have named ranges in the question title.  Maybe you misunderstood the question or I am not following your reply?  The goal is, from the active cell, name the two ranges, and leave the active cell as it is.  Unless the macro filters than names then unfilters?  That is why I am even wondering if this can be done.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41751544
As per your description, I assumed that your ultimate goal is to paste the Qtys and CatCodes of the selected group onto the Sheet2 and to do that creating named ranges is not required. I am not sure why do you want to do that?

BTW try the following tweaked code, this will create two named ranges as per the selected group in col. A.

Is this what you are trying to achieve?
Sub CopyPasteAsPerSelectionInColumnA()
Dim sws As Worksheet, dws As Worksheet
Dim sRow As Long, eRow As Long

Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")

If Selection.Parent.Name <> sws.Name Then
    MsgBox "Please select the cell on " & sws.Name & " and then try again...", vbExclamation, "Wrong Sheet Selected!"
    Exit Sub
End If
If Selection.Column <> 1 Then
    MsgBox "Please select a cell in column A and then try again...", vbExclamation, "Wrong Selection!"
    Exit Sub
End If

sRow = sws.Range("A:A").Find(what:=ActiveCell.Value).Row
eRow = sws.Range("A:A").Find(what:=ActiveCell.Value, searchdirection:=xlPrevious).Row
dws.Cells.Clear
sws.Range("B" & sRow & ":B" & eRow).Name = "Qtys"
sws.Range("C" & sRow & ":C" & eRow).Name = "CatCode"
sws.Range("B" & sRow & ":C" & eRow).Copy dws.Range("A1")
dws.Columns.AutoFit
dws.Activate
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 41751602
Neeraj
This is looking very promising.  Having issues with: Ln20  dws.Cells.Clear

Can we delete all rows under paste?  so if it puts in 6 rows of data, delete all rows under that last one.  I chg'd the paste destination to  G2 but that should not do much.  Is that possible to have a clean paste into the new sheet?
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41751649
Line#20 clears the sheet2 before new data is pasted onto it.
Do you mean pasting onto a new sheet each time the code is executed?

I am having a hard time to understand your description. Can you upload a sample workbook mocking up the desired output mentioning the each step you want to automate?
0
 

Author Comment

by:RWayneH
ID: 41751978
I am going to try a different clear prior to pasting them in.. that may solve the issue.  Need to do a little more testing..  Will let you know later today.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41752464
There is another method, but it also requires the columns to have headers.  Shall I post that solution?
0
 

Author Comment

by:RWayneH
ID: 41752495
thanks aikimark.. but I believe the one posted is going to work still testing it.. but all is good so far
0
 

Author Closing Comment

by:RWayneH
ID: 41752680
Excellent!!!  Thanks for the help.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41752776
You're welcome. Thanks for the feedback.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel compare strings 6 55
Calculating Sales Tax 13 68
Excel -  lookup two values and return a value 3 30
Format Control on two Buttons 6 25
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question