Delete blank cells if column A or column M has a value

[dininaus]

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

 

[footoo]

Maybe :

On Error Resume Next
With Columns("A")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[12]="""",1/0,"""")"
    .SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    .SpecialCells(xlCellTypeFormulas).ClearContents
End With
On Error GoTo 0

 

[dininaus]

Maybe :

On Error Resume Next
With Columns("A")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[12]="""",1/0,"""")"
    .SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    .SpecialCells(xlCellTypeFormulas).ClearContents
End With
On Error GoTo 0

Thank you for your prompt response, I tried your code but it didn't work

 

[footoo]

Thank you for your prompt response, I tried your code but it didn't work

In what way did it not work? (Works for me.)

 

[dininaus]

In what way did it not work? (Works for me.)

Sorry, it is not creating the .csv after the macro is executed

 

[Fluff]

Cross posted Delete blank rows if column A or column M has a value

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[dininaus]

Cross posted Delete blank rows if column A or column M has a value

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Apologies, wasn't aware of that

 

[Fluff]

Can you please supply link(s) to all other sites where you have asked this question. Thanks

 

[dininaus]

https://www.reddit.com/r/excel/comments/fr0yqz

 

[Fluff]

Thanks