Macro to Open and Delete specific Columns

[Aswinraj]

Hi Friends,

I Need to Open an Excel file (.CSV Format file) based on Prefix Name "Rawdata" (Todaydate)" - Where Today date will be changed on daily basis.
Select all data Copy and paste it in New excel file.
Now need to delete specific columns "Model Desc" and "Product Desc" - Please help me.

[/FONT][/COLOR][COLOR=#252C2F][FONT=Courier]Sub Macro1()[/FONT][/COLOR]
Application.DisplayAlerts = False
 Dim rng As Range
Set TodayDShip = Workbooks.Open("C:\Users\aasw\Desktop\DShip\Rawdata.csv")
               Set rng = Sheet1.Range("A1:BB1").Find(What:="Model Desc", _
            LookAt:=xlWhole, MatchCase:=False)
 If Not rng Is Nothing Then
        rng.EntireColumn.Delete
        End If 
End Sub[COLOR=#252C2F][FONT=Courier]
[/FONT][/COLOR][COLOR=#252C2F][FONT=Helvetica]

With Regards,
Aswinraj A

 

[igold]

Your description and your code are a little confusing. Perhaps you could provide an example of what the exact file name would be and the exact path where it would be found would be. How about using today 1/25/2019 as the date.

 

[Aswinraj]

Your description and your code are a little confusing. Perhaps you could provide an example of what the exact file name would be and the exact path where it would be found would be. How about using today 1/25/2019 as the date.

Hi igold,


I guess my post was not clear.., but some how i find a solution to open excel file and delete unnecessary rows but now i need a solution to "Filter and Delete rows which are visible".

ie., Filter Column - "Forecast Method", Criteria - "D" and Delete Visible cells except Header.

Below is the code i tried but not working

'To Open File


Sub DShip()
    Application.DisplayAlerts = False
    cdir = ThisWorkbook.path & "\"
    MsgBox ("Select DShip Raw Data")
    On Error Resume Next
        RawData = Application.GetOpenFilename(, , "Select DShip Raw Data", , False)
        Workbooks.Open fileName:=RawData
        str1 = ActiveWorkbook.Name
    
        Columns("N:O").EntireColumn.Delete


'Filter and delete visible cells


        ActiveSheet.Range("$A$1:$AH$24865" & Lines).AutoFilter Field:=4, Criteria1:= "D"
        ActiveSheet.Range("$A$1:$AH$24865" & Lines).Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete


End Sub

 

[igold]

Hi,

I only altered the code below the red comment, for the AutoFilter purposes.

I hope that's what you wanted.

'To Open File




Sub DShip()
    Application.DisplayAlerts = False
    cdir = ThisWorkbook.Path & "\"
    MsgBox ("Select DShip Raw Data")
    On Error Resume Next
        RawData = Application.GetOpenFilename(, , "Select DShip Raw Data", , False)
        Workbooks.Open Filename:=RawData
        str1 = ActiveWorkbook.Name
    
        Columns("N:O").EntireColumn.Delete


'Filter and delete visible cells
[COLOR=#ff0000]    'New Code Below:[/COLOR]
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Range("$A$1:$AH$24865").AutoFilter Field:=4, Criteria1:="D"
    ActiveSheet.Range("$A$1:$AH$24865").Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False


End Sub

I hope this helps.

 

[Aswinraj]

Hi.., Thank you it worked now finally.

Hi,

I only altered the code below the red comment, for the AutoFilter purposes.

I hope that's what you wanted.

'To Open File




Sub DShip()
    Application.DisplayAlerts = False
    cdir = ThisWorkbook.Path & "\"
    MsgBox ("Select DShip Raw Data")
    On Error Resume Next
        RawData = Application.GetOpenFilename(, , "Select DShip Raw Data", , False)
        Workbooks.Open Filename:=RawData
        str1 = ActiveWorkbook.Name
    
        Columns("N:O").EntireColumn.Delete


'Filter and delete visible cells
[COLOR=#ff0000]    'New Code Below:[/COLOR]
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Range("$A$1:$AH$24865").AutoFilter Field:=4, Criteria1:="D"
    ActiveSheet.Range("$A$1:$AH$24865").Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False


End Sub

I hope this helps.

 

[igold]

You're welcome. Thanks for the feedback.