Home I have a list of excel filenames in the column A of my worksheet and I would like to remove the .xls and .xlsm tags from the filenames using VBA. How?
Reply: 1

I have a list of excel filenames in the column A of my worksheet and I would like to remove the .xls and .xlsm tags from the filenames using VBA. How?

sat
1#
sat Published in 2017-12-07 18:50:49Z

I want to remove the .xls and .xlsm tags using the "." in the VBA logic, but I am a beginner in VBA and I am wondering how I can accomplish the objective ?

Maldred
2#
Maldred Reply to 2017-12-07 23:47:45Z

This should do it... Just pass in the range that you want to extract from

Sub RemoveExtension(rng As Range)
   ' Declare your variables
   Dim LR As Long
   Dim i As Long
   Dim str() As String

   With rng
      ' Find the last row
      LR = .Cells(.Rows.Count, 1).End(xlUp).Row
      ' Enter loop
      For i = LR To 1 Step -1
         If Not (IsEmpty(.Cells(i))) Then
            ' Extract and split text using "." as a delimiter
            str() = Split(.Cells(i).Value, ".")
            ' Rewrite text in cell from first array variable in str()
            .Cells(i).Value = str(0)
         End If
      Next i
   End With
End Sub

You can call this function with another Sub function... Example below:

Sub Macro1()

    ' Change Range() to what every range you need
    Call RemoveExtension(Range("A2:A10"))

End Sub

Bare in mind this only works on columns, so you cannot pass in Range("A1:C3")

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30512 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO