Deleting Rows

[DougDR]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0mm 5.4pt 0mm 5.4pt; mso-para-margin:0mm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi again everyone, hope you don’t mind helping again.

After creating a CSV file I need to open it and do three (3) things.
First:- to delete all rows which have a certain value in columnA.
Then:- to delete columns A and B
Then:- to format all cells not to have “wrap text”

Thank in advance for your help

PS Oh yes. I don’t know if VBA code is filed with a CSV file?

 

[JackDanIce]

As a helpful tip, you should try to be more precise when asking for help, i.e. what value in A determins if the row is to be deleted?

Anyway, try this code:

Sub FeeFiFoThumb ()
 
Dim i as Long, j as Long
 
With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With
 
i = Range("A" & Rows.Count).End(xlUp).Row
j = Cells(1,Columns.Count).End(xlToLeft).Column
 
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
 
With Range("A1", Cells(i,j))
  .AutoFilter
  .AutoFiler Field:=1, Criteria1:="1"
' Change the 1 to whatever value you want to filter to delete
  .Offset(1,0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
 
Columns("A:B").Delete
 
Cells.WrapText = False
 
With Application
  .ScreenUpdating = True
  .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[DougDR]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0mm 5.4pt 0mm 5.4pt; mso-para-margin:0mm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Thank you so much JackDanIce. Sorry about that, the value will be "FALSE".

I have the following code that save a sheet to a csv file format without changing the excel file that I am working with. After it is saved I need to open it and run your code. But??? how do I get your code to save with the saved csv file?

Also I tried to create a macro to be saved in the "Personal macro workbook" but it come up with "
Personal Macro Workbook in the startup folder must stay open for recording" What do you think is wrong?



What I would like to do is place an icon for your code on a toolbar?


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2011/06/10 by DOUG DAVEY
'
' Keyboard Shortcut: Ctrl+c
'Declare variable
Dim strPath As String

'Create a new workbook containing Sheet1 (change accordingly)
ThisWorkbook.Sheets("CSVListing").Copy

'Assign path to original workbook
strPath = ThisWorkbook.Sheets("CSVListing").Range("A1")

ActiveSheet.Select

'Save new workbook as CSV file
ActiveWorkbook.SaveAs Filename:="E:\My Documents\Bid or Buy\New SearchDesign Stock\CSV Files\" & strPath & ".csv", FileFormat:=xlCSV

'Close new workbook
ActiveWorkbook.Close savechanges:=False
End Sub

As a helpful tip, you should try to be more precise when asking for help, i.e. what value in A determins if the row is to be deleted?

Anyway, try this code:

Sub FeeFiFoThumb ()
 
Dim i as Long, j as Long
 
With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With
 
i = Range("A" & Rows.Count).End(xlUp).Row
j = Cells(1,Columns.Count).End(xlToLeft).Column
 
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
 
With Range("A1", Cells(i,j))
  .AutoFilter
  .AutoFiler Field:=1, Criteria1:="1"
' Change the 1 to whatever value you want to filter to delete
  .Offset(1,0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
 
Columns("A:B").Delete
 
Cells.WrapText = False
 
With Application
  .ScreenUpdating = True
  .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[JackDanIce]

Change this line in my code:

  .AutoFiler Field:=1, Criteria1:="1"

To

  .AutoFiler Field:=1, Criteria1:=FALSE

Not too sure about the rest of what you're asking as I've never had that problem with my personal book and saving generic macros I use all the time, sorry. Hopefully someone else can help/answer

 

[Weaver]

If you want to more thoroughly embed the code, consider creating an addin. You might have to do a bit of research for this, but it's tidier, will always open, even when using .csv files and you can assign the code to a toolbar button.

 

[DougDR]

Thanks again. you just don't know how much you have helped. thank you again!!!!!...

 

[DougDR]

Hi Weaver.
Thanks so much, but I think you have now gone completly over my head. I think I need to learn a lot more before trying "Add-ins" If I could just fix the problem with saving a macro to "Personal macro workbook" and not get the error mesage " Personal Macro Workbook in the startup folder must stay open for recording" I hope that this will solve my problemhttp://www.mrexcel.com/forum/member.php?u=102146

 

[DougDR]

Hi JackDanice
I keep getting a error in with and without ""

.AutoFiler Field:=1, Criteria1:=FALSE</pre>http://www.mrexcel.com/forum/member.php?u=139270

 

[JackDanIce]

Have you tried "FALSE" or ="=FALSE"

 

[DougDR]

Yes and have just tried the "=....." It still shows up as an error.

Thank for helping me out. its so great