Posts by jonny

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

Sub RemoveDuplicatesAndSort()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long
    Dim j As Long
    Dim temp As String

    Set ws = ThisWorkbook.Sheets("Sheet1") ' change "Sheet1" to your sheet name
    lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' get the last row in column A

    ' loop through rows and delete duplicates
    For i = lRow To 2 Step -1
        temp = ws.Cells(i, "A").Value & ws.Cells(i, "B").Value
        For j = i - 1 To 2 Step -1
            If ws.Cells(j, "A").Value & ws.Cells(j, "B").Value = temp Then
                ws.Rows(j).Delete
            End If
        Next j
    Next i

    ' sort the data by column A and then column B
    ws.Range("A2:B" & lRow).Sort Key1:=ws.Range("A2"), Order1:=xlAscending, _
        Key2:=ws.Range("B2"), Order2:=xlAscending, Header:=xlNo
End Sub

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:

attachFile = Application.GetOpenFilename("Files (*.**), *.**", MultiSelect:=True)

If Not IsArray(attachFile) Then
    ' single file was selected
    Set xOutlookObj = CreateObject("Outlook.Application")
    Set xEmailObj = xOutlookObj.CreateItem(0)
    With xEmailObj    
        .Display    
        .To = ""    
        .CC = ""    
        .Subject = ""    
        'use .HTMLBody to enable signature    
        .HTMLBody = strbody & .HTMLBody
        .Attachments.Add attachFile
    End With
Else
    ' multiple files were selected
    For i = LBound(attachFile) To UBound(attachFile)
        ' attach each file
        Set xOutlookObj = CreateObject("Outlook.Application")
        Set xEmailObj = xOutlookObj.CreateItem(0)
        With xEmailObj    
            .Display    
            .To = ""    
            .CC = ""    
            .Subject = ""    
            'use .HTMLBody to enable signature    
            .HTMLBody = strbody & .HTMLBody
            .Attachments.Add attachFile(i)
        End With
    Next i
End If

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

Thank you, guys, looks as Power Query is the best solution.

However my website requires login credentials.

Without of it getting a screen:

When I am trying to log-in, getting another error:

Hi Guys,

I wish to bring a data from a website table (table on 289 pages), how I do it?

Thank you

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?

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

Hi Carim,

Just saw your (somehow I have not been notified) reply and tested it.

Works exactly as expected, fast and smooth, great job!

Thanks so much!

Hi Carim,

Just saw your (somehow I have not been notified) reply and tested it.

Works exactly as expected, fast and smooth, great job!

Thanks so much!

No, unfortunately, no ant draft UDF. Conceptually , I was using a regex to split letter and numbers, however I'm not good with a code syntax.

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:

The UDF should return same location, but in short form (refer to column C)

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:

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

Guys, need your help with writing a UDF that gets a string and convert it into numeric value.

Rules:

R => "."

K => multiply by 1000

M => multiply by 1000000

Examples:

38R2 = 38.2

45K2 = 45200

1M5 = 1500000

Thank you,

Can I restrict access to certain cell by using Environ("USERNAME") rather than password?

Looking for a way to restrict/allow access to column/cell (range) by certain user.

It's required for a table where data in each column is approved/reject by certain function.

There is a need to replace cell with consecutive strings by "-" delimiter.

Example:
- Input: R1, R2, R3, R4, R7, R9, R10
- Output: R1-R4, R7, R9-R10

Please your kind help with a UDF code.

Quote from jonny

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:

Private Sub Workbook_Open()
    Dim pass, valid As String
    
    Windows(ThisWorkbook.Name).Visible = False


    pass = "XXX"
    valid = InputBox("Password?")


    If Environ("USERDOMAIN") <> "YYY" Or valid <> pass Then
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
    Else
        Windows(ThisWorkbook.Name).Visible = True
    End If
End Sub

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

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..

Exactly what I was missing for. Thank you!