This is an old revision of the document!
Excel
Macros
References
Find and Replace
Dim sht As Worksheet Dim fnd As Variant Dim rplc As Variant fnd = "#REF!" rplc = "" For Each sht In ActiveWorkbook.Worksheets sht.Cells.Replace what:=fnd, Replacement:=rplc, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False Next sht
Option Explicit Public Sub ExportWorksheetAndSaveAsCSV() 'On Error GoTo Errorcatch Application.ScreenUpdating = False Dim wbkExport As Workbook Dim shtToExport As Worksheet Dim myWorksheet As Variant Dim myFolder As Variant Dim myFilename As Variant Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long myWorksheet = "tmpSheet" myFolder = ThisWorkbook.Worksheets("Summary").Range("B2").Value myFilename = ThisWorkbook.Worksheets("Summary").Range("B3").Value Sheets(myWorksheet).Visible = True Set shtToExport = ThisWorkbook.Worksheets(myWorksheet) 'Sheet to export as CSV Set wbkExport = Application.Workbooks.Add shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count) 'Convert to Values (do not copy Formulas) ActiveSheet.UsedRange.Value = shtToExport.UsedRange.Value Application.DisplayAlerts = False 'Possibly overwrite without asking 'MsgBox "The name of the active sheet is " & ActiveSheet.Name 'MsgBox "The name of the active Workbook is " & ActiveWorkbook.Name With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed 'ViewMode = ActiveWindow.View 'ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed '.DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value = "0" Then .EntireRow.Delete 'This will delete each row with the Value "0" 'in Column A, case sensitive. End If End With Next Lrow End With With ActiveWorkbook 'Saves the new workbook to given folder / filename: .SaveAs Filename:=myFolder & myFilename, FileFormat:=xlCSV 'Closes the file .Close False End With Application.DisplayAlerts = True Application.ScreenUpdating = True Sheets(myWorksheet).Visible = False MsgBox "Created Dialer File: " & myFilename 'Errorcatch: MsgBox Err.Description End Sub