Macro to Open and Delete specific Columns

Aswinraj

Board Regular
Joined
Dec 10, 2015
Messages
65
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.

Code:
[/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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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 :(

Code:
'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
 
Last edited:
Upvote 0
Hi,

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

I hope that's what you wanted.

Code:
'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.
 
Upvote 0
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.

Code:
'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.
 
Upvote 0
You're welcome. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top