specialist
04-09-2015, 10:49 AM
Hello,
I'm in need of a macro that will delete all the empty lines and columns within the spreadsheet. I need all empty lines after (and including) line 17 and all empty columns after (and including) Column V. I want to be able to add this to the existing macro attached. Also, the deleting would have to be done before anything else withing the existing macro.
The manual way I would do this would be to select the first empty line after the data (Ctrl+Shift+arrow key down), right click and delete. Same process for the columns. Then I would run the macro below
Thank you
Sub CreateRebateFile()
Const TAB_DELIMITED = 3
Const EXCEL_WORKSHEET = 1
Dim msgResponse As Variant
On Error GoTo CreateRebateFile_Error
'** Save the workbook before creating the individual files
If ActiveWorkbook.Saved = False Then
msgResponse = MsgBox("Do you want to Save this Excel Workbook? (Recommended)", _
vbOKCancel + vbDefaultButton1 + vbQuestion, "Rebate File for SAP")
If msgResponse = vbOK Then
Application.Dialogs(xlDialogSaveAs).Show "", EXCEL_WORKSHEET
End If
End If
For Each Worksheet In Worksheets
Worksheet.Select
'** Offer to Save the Excel File
If Range("Customer_Number").Value > " " Then
If Range("Period_FROM").Value > " " And _
Range("Period_To").Value > " " Then
If Range("START_CELL").Value > " " Then
msgResponse = MsgBox("Would you like to Create the SAP Upload File for Worksheet: " _
& Worksheet.Name & " ?", _
vbOKCancel + vbDefaultButton1 + vbQuestion, _
"Rebate File for SAP")
If msgResponse = vbOK Then
Application.Dialogs(xlDialogSaveAs).Show "", TAB_DELIMITED
End If
Else
MsgBox "No Claims entered in Worksheet: " & Worksheet.Name, _
vbOKCancel + vbExclamation, "Rebate Save Error"
Range("START_CELL").Select
End If
Else
MsgBox "Rebate Period Incomplete or Invalid for Worksheet: " & Worksheet.Name, _
vbOKCancel + vbExclamation, "Rebate Save Error"
Range("Period_FROM").Select
End If
Else
MsgBox "Customer Number Missing in Worksheet: " & Worksheet.Name, _
vbOKCancel + vbExclamation, "Rebate Save Error"
Range("Customer_Number").Select
End If
Next
Exit Sub
CreateRebateFile_Error:
MsgBox "UnExpected Error Occurred During Conversion: " & vbCrLf & _
"#" & Err.Number & ": " & Err.Description, vbOKOnly + vbExclamation, "Rebate File for SAP"
Err.Clear
Exit Sub
End Sub
I'm in need of a macro that will delete all the empty lines and columns within the spreadsheet. I need all empty lines after (and including) line 17 and all empty columns after (and including) Column V. I want to be able to add this to the existing macro attached. Also, the deleting would have to be done before anything else withing the existing macro.
The manual way I would do this would be to select the first empty line after the data (Ctrl+Shift+arrow key down), right click and delete. Same process for the columns. Then I would run the macro below
Thank you
Sub CreateRebateFile()
Const TAB_DELIMITED = 3
Const EXCEL_WORKSHEET = 1
Dim msgResponse As Variant
On Error GoTo CreateRebateFile_Error
'** Save the workbook before creating the individual files
If ActiveWorkbook.Saved = False Then
msgResponse = MsgBox("Do you want to Save this Excel Workbook? (Recommended)", _
vbOKCancel + vbDefaultButton1 + vbQuestion, "Rebate File for SAP")
If msgResponse = vbOK Then
Application.Dialogs(xlDialogSaveAs).Show "", EXCEL_WORKSHEET
End If
End If
For Each Worksheet In Worksheets
Worksheet.Select
'** Offer to Save the Excel File
If Range("Customer_Number").Value > " " Then
If Range("Period_FROM").Value > " " And _
Range("Period_To").Value > " " Then
If Range("START_CELL").Value > " " Then
msgResponse = MsgBox("Would you like to Create the SAP Upload File for Worksheet: " _
& Worksheet.Name & " ?", _
vbOKCancel + vbDefaultButton1 + vbQuestion, _
"Rebate File for SAP")
If msgResponse = vbOK Then
Application.Dialogs(xlDialogSaveAs).Show "", TAB_DELIMITED
End If
Else
MsgBox "No Claims entered in Worksheet: " & Worksheet.Name, _
vbOKCancel + vbExclamation, "Rebate Save Error"
Range("START_CELL").Select
End If
Else
MsgBox "Rebate Period Incomplete or Invalid for Worksheet: " & Worksheet.Name, _
vbOKCancel + vbExclamation, "Rebate Save Error"
Range("Period_FROM").Select
End If
Else
MsgBox "Customer Number Missing in Worksheet: " & Worksheet.Name, _
vbOKCancel + vbExclamation, "Rebate Save Error"
Range("Customer_Number").Select
End If
Next
Exit Sub
CreateRebateFile_Error:
MsgBox "UnExpected Error Occurred During Conversion: " & vbCrLf & _
"#" & Err.Number & ": " & Err.Description, vbOKOnly + vbExclamation, "Rebate File for SAP"
Err.Clear
Exit Sub
End Sub