+ Reply to Thread
Results 1 to 14 of 14

Adjust VBA macro for a fixed location and remove user input

  1. #1
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Adjust VBA macro for a fixed location and remove user input

    The macro below does perform the task I need correctly. It takes the values from column A&B and insert them into column C. The Input Box entries are A:B on the first and C1 on the second one. I would like to remove the input boxes and have the macro run without user input. The sheet will always be Date_Validation using columns A:B with an input into C. The macro below uses C1 as the input cell where in column C both A&B columns are joined with only unique values remaining. The macro does appear to calculate for a header since you select C1 but the data starts in C2. Can this be cleaned up to just run without input. I don't understand the code below, any help would be great so I can learn
    Thanks

    Sub FindUniques()
    'Updateby20140313
    Sheets("Date_Validation").Select
    'Added Range to test
    Range("A:B").Select
    Dim rng As Range
    Dim InputRng As Range, OutRng As Range
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range A:B :", xTitleId, InputRng.Address, Type:=8)
    Set OutRng = Application.InputBox("Output to ( C1 ):", xTitleId, Type:=8)
    Set dic = CreateObject("Scripting.Dictionary")
    For j = 1 To InputRng.Columns.Count
    For i = 1 To InputRng.Rows.Count
    xValue = InputRng.Cells(i, j).Value
    If xValue <> "" And Not dic.Exists(xValue) Then
    OutRng.Value = xValue
    dic(xValue) = ""
    Set OutRng = OutRng.Offset(1, 0)
    End If
    Next
    Next
    End Sub

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,012

    Re: Adjust VBA macro for a fixed location and remove user input

    .
    The macro can be edited to use without the input boxes ... however, you need to be more specific.

    What rows in Col A & B are you targeting and will the data always be pasted to C1 ?

  3. #3
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Adjust VBA macro for a fixed location and remove user input

    The attached macro did not require a row length. I have formulas down to row 7500 for both columns. Data in A&B will vary daily. Currently down to row 7500 is covering the largeest data set entered. A range of A2 thru B7500 would handle it if you need a range. The attached macro required a selection of C1 to ccopy the data from column A & B, remove the duplicates and paste starting cell C2. The above macro accounted for a header. The C column and cell C2 will always be where the combined data will go

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Adjust VBA macro for a fixed location and remove user input

    How about this ?

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  5. #5
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Adjust VBA macro for a fixed location and remove user input

    I tried your macro suggestion and it appeared slow and column C had blanks mixed in with the data pasted. The original macro does work perfectly, but requires user input. Is there any way to adjust that one. Input will always be from A:B and output to C1 based on this macro code which has an offset to actually enter the data in C2 when final pasting. I adjusted the message box statement to state using A:B and C1. I didnt understand the code to have these preselected Thanks

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Adjust VBA macro for a fixed location and remove user input

    I'm pretty sure this is much faster then what you have.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Adjust VBA macro for a fixed location and remove user input

    Hello, I ran this macro today with the changes. It does run quickly. There are headers in all columns including A,B,C. I changed the Range("c1") to c2 which did correct that issue. The only problem I am still having is the the final data, the combined values from A& B into C after removing duplicates(Only Unique Values) is adding one blank cell in the middle of the data in Column C. Is there a way to not have this occur? Thanks

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Adjust VBA macro for a fixed location and remove user input

    That most likely means that there's a cell in your range that isn't really empty, probably there's a space in it so it appears empty.
    Is that the case ?

  9. #9
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Adjust VBA macro for a fixed location and remove user input

    Hello, I ran the new data set again today and still get the same error where a blank cell is mixed into Column C. I checked the cell for content and couldn't find any. Would you know why this occurs and how this can be avoided. The data is new each day in columns A:B. Would a sort function prior to pasting just add the blank to the end?

  10. #10
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Adjust VBA macro for a fixed location and remove user input

    Without seeing the data, no.
    Also added range clearance since you say data changes each day.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Adjust VBA macro for a fixed location and remove user input

    Hi, I tried this version. The macro fails in the lines below

    With Sheets("Date_Validation")
    .Range("C2", .Range("C" & .Rows.Count).End(xlUp)).ClearContents
    .Range("C2").Resize(.Count) = Application.Transpose(Dic.keys)
    End With

    The 1st line starting with .Range("C2") is clearing out the header and the second .range line is bugging. The macro stops and highlights the 2nd line.

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Adjust VBA macro for a fixed location and remove user input

    Amended the code in your 1st post (you said that worked fine) since we're getting nowhere without example file.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-01-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    63

    Re: Adjust VBA macro for a fixed location and remove user input

    Thank You Its not as fast but worked fine

  14. #14
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Adjust VBA macro for a fixed location and remove user input

    OK, last try.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count specific day between fixed dates, with user input controlling time frame, month/s
    By Joe Bob Crain in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-12-2017, 04:16 PM
  2. [SOLVED] How to create a static progress bar that is fixed and responds to user input manually
    By chin67326 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-29-2016, 04:19 AM
  3. [SOLVED] User input for save location?
    By fourmurphys in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2013, 03:25 PM
  4. Convert freeform user input and write to a fixed length text file
    By vijaymohan10 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2013, 04:44 AM
  5. User input for folder location
    By jmckee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-02-2010, 03:09 PM
  6. Copy data from user input location
    By doc19 in forum Excel General
    Replies: 6
    Last Post: 03-19-2009, 02:15 PM
  7. Need macro to insert chart w/ FIXED size & location
    By Matt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1