Hello there Tino,
Heres the code i have managed to come up with. Its a little long since there were quite a few requirements you needed:
To summarise your requirements
1) Open up all the workbooks originating from your workbook's folder and do the following:
2) Run through all cells in each worksheet and delete all #N/A cells
3) Run through each worksheet again and look for empty rows AND columns and delete them where-ever they occur
4) Re-save the workbook back to the location which the workbook originated (Here: i resaved the file under a different name, i.e., if the workbook is "workbook1", it is resaved as "workbook1-cleaned", in case you need to refer to the original file...)
For the requirement (3), my code in red below is a little bit lengthy; im not sure if anyone else in the forum might have a more concised solution, but it still works at least when i tried on some dummy data.
Try the below and let us know 
Run the macro in a workbook that is saved in a folder where the other workbooks you need to clean up are in
Option Explicit
Sub CleanFiles()
Dim wb As Workbook
Dim strFilePath As String
Dim strFile As String
Dim strNewFileName As String
Dim ws As Worksheet
Dim rngError As Range
Dim LastRow As Range
Dim LastCol As Range
Dim TestCol As Range
Dim TestRow As Range
strFilePath = ThisWorkbook.Path & "\"
strFile = Dir(strFilePath & "*.xlsx")
Application.ScreenUpdating = False
Do While strFile <> ""
Set wb = Workbooks.Open(strFilePath & strFile) 'Open each xlsx file in folder
For Each ws In wb.Worksheets
Set rngError = ws.UsedRange.Find(What:="#N/A", LookIn:=xlValues) 'find each error cell
Do Until rngError Is Nothing
rngError.ClearContents 'delete each error cell
Set rngError = ws.UsedRange.FindNext(rngError)
Loop
[COLOR=#FF0000] Set LastCol = ws.Cells(1, Columns.Count).End(xlToLeft)
Set LastRow = ws.Cells(Rows.Count, 1).End(xlUp)
Set TestCol = ws.Range("A1").End(xlToRight)
Set TestRow = ws.Range("A1").End(xlDown)
Do Until TestCol = LastCol 'Do a loop to run through each worksheet to check an empty column and del it
If WorksheetFunction.CountA(ws.Columns(TestCol.Offset(0, 1).Column)) = 0 Then
ws.Columns(TestCol.Offset(0, 1).Column).Delete
End If
Set TestCol = TestCol.End(xlToRight)
Loop
Do Until TestRow = LastRow 'Do a loop to run through each worksheet to check an empty row and del it
If WorksheetFunction.CountA(ws.Rows(TestRow.Offset(1, 0).Row)) = 0 Then
ws. Rows(TestRow.Offset(1, 0).Row).Delete
End If
Set TestRow = TestRow.End(xlDown)
Loop[/COLOR]
Next ws
strNewFileName = Left(wb.Name, InStr(wb.Name, ".") - 1) & "-Cleaned" 'create a new file with name and -cleaned
wb.SaveAs Filename:=strFilePath & strNewFileName
wb.Close
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
Display More
