Posts by jonny

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    To delete duplicate rows and rearrange the data in the way you described, you can use the following VBA code:

    To use this code, copy and paste it into the VBA editor and then run it by pressing the "Run" button or by pressing F5. Make sure to change the sheet name in line 6 to the name of the sheet where your data is located.

    I hope this helps! Let me know if you have any questions.



    To attach multiple files using the code you provided, you will need to iterate over the array of file names returned by the GetOpenFilename function. Here's an example of how you can do this:


    I hope this helps! Let me know if you have any questions.



    I'm trying to copy a table from the website to Excel,

    The website require login with username and password.


    How I embed that within the code?

    Code
    ActiveWorkbook.Queries.Add Name:="xxx", Formula:= _
    
    "let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://.......""))," & Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Data0"
    
    ActiveWorkbook.Worksheets.Add

    Thank you

    Sorry, Carim, just saw your code. Thank you.

    Don't know why , but somehow I'm not getting notifications once the answer is posted.


    Anyway the code is not grouping consecutive locations. By applying a code on U2, U6, U12, U13 I've gotten U12, U13, U2, U6.

    Where the expected result is U2, U6, U12-U13

    The file cannot be attached due to legal issues, however here is the file content in table format:


    Component Location Location (Short Form)
    COMP1 R1, R2, R4, R4, R5, R6, R7, R8, R9, R10 R1-R10
    COMP2 C98, C99, C103, C104, C105, C109, C11, C111, C115, C116, C117, C121 C98-C99, C103-C105, C115-C117, C121-C125, C129-C130
    COMP3 U2, U6, U12, U13 U2, U6, U12-U13
    COMP4

    Each electronic components on electronics PCB has a reference designator (unique location). The reference designator usually consists of one or two letters followed by a number, e.g. R13, C1002.

    I'm trying to create a UDF that gets a string of locations and returns a group of consecutive locations as shown:

    Examples:

    1. C1, C2, C3 ==> C1-C3

    2. R4, R1, R7, R5, R3 ==> R1, R3-R4, R7

    3. LD100, LD101, LD102, LD103, LD104, LD105, LD107 ==> LD100-LD105, LD107

    Thanks, RoyUK, much appreciate it!


    Turned your code into UDF:

    Code
    Function CONVERTNUMERIC(rCl String) As Double
                If InStr(1, rCl , "R") > 0 Then CONVERTNUMERIC= Replace(rCl , "R", ".")
                If InStr(1, rCl , "K") > 0 Then CONVERTNUMERIC= Replace(rCl , "K", "") * 100
                If InStr(1, rCl , "M") > 0 Then CONVERTNUMERIC= Replace(rCl , "M", "") * 100000
    End Function

    I wish to apply a password for internal documents (PPTX, DOCX, XLSX..) , that will not be opened out of company.
    I assume should be some some Window Log-In validation.


    Please your help with the code..


    Here is it to all that may be interested in Excel:



    Any idea for a same solution in PPT and DOC files?