Hi all,
I have below macro which checks if any value exists in column A then delete the empty rows and copy-paste all the other cells with values to a new sheet and save as .csv.
Could someone please help me to amend the code so it can check if any value exists in either column A or column M (instead of just column A)
Thanks in advance
Sub OPSXLSMToCSV()
Dim myCSVFileName As String
Dim myWB As Workbook
Dim tempWB As Workbook
Dim rngToSave As Range
Dim ws As Worksheet
myDateStamp = Format(Now(), "ddmmyyyyhhmmss")
Set myWB = ThisWorkbook
myCSVFileName = "C:\Users\Desktop\Test\" & "Add_" & Environ("Username") & "_" & myDateStamp & ".csv"
On Error Resume Next
Set rngToSave = Range("A1:AE1002")
rngToSave.Copy
Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.SaveAs FileName:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
MsgBox ("CSV File Created")
err:
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
Application.Quit
End Sub
I have below macro which checks if any value exists in column A then delete the empty rows and copy-paste all the other cells with values to a new sheet and save as .csv.
Could someone please help me to amend the code so it can check if any value exists in either column A or column M (instead of just column A)
Thanks in advance
Sub OPSXLSMToCSV()
Dim myCSVFileName As String
Dim myWB As Workbook
Dim tempWB As Workbook
Dim rngToSave As Range
Dim ws As Worksheet
myDateStamp = Format(Now(), "ddmmyyyyhhmmss")
Set myWB = ThisWorkbook
myCSVFileName = "C:\Users\Desktop\Test\" & "Add_" & Environ("Username") & "_" & myDateStamp & ".csv"
On Error Resume Next
Set rngToSave = Range("A1:AE1002")
rngToSave.Copy
Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.SaveAs FileName:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
MsgBox ("CSV File Created")
err:
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
Application.Quit
End Sub