0

For reasons beyond my control, I must save and upload my data as a csv and i must manually select and clear the cells below my data set, the range changes daily.

Question: How can I edit my vba script to create a range based on the cell below my data set to :J10 and clear/delete it? EDIT: Are there any changes you would implement?

Here's what I currently have, after exporting and saving as a csv, I reopen the new data and sort and attempt to clear. I have already tried Selection.SpecialCells(CellTypeBlanks).EntireRow.ClearContents & Selection.SpecialCells(CellTypeBlanks).ClearContents

I have 5 separate csv workbooks this macro currently opens and operates on but here is the final portion of my script that is giving me errors

 Sub openCSV()
    Dim lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = lRow To 2 Step -1
        If ActiveSheet.Range("H" & i).Value = 0 Then
            ActiveSheet.Rows(i).ClearContents
        End If
    Next i

    Range("A2:J10").Select
    ActiveWorkbook.Worksheets("BA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("BA").Sort.SortFields.Add Key:=Range("H2") _
                                                              , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("BA").Sort
        .SetRange Range("A2:J10")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    On Error Resume Next
    Range("A2:H10").Select
    Selection.SpecialCells(CellTypeBlanks).ClearContents
    Range("A2:J10").Select
    Selection.SpecialCells(CellTypeBlanks).EntireRow.ClearContents

    ActiveWorkbook.Save
    ActiveWorkbook.Close
    SaveChanges = True

Thank you for your time and assistance

QHarr
  • 83,427
  • 12
  • 54
  • 101
john
  • 3
  • 2
  • 6
  • 2
    Get rid of the On Error Resume Next and the .Select. Add a countblank test to see if blanks present before attempting specialcells. – QHarr Aug 21 '18 at 15:42
  • What are the errors? And on which lines? See [avoid .Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [finding last row](https://www.rondebruin.nl/win/s9/win005.htm) – QHarr Aug 21 '18 at 15:46
  • When uploading to our server, it states lines 7&8 are corrupt, which correspond to the lines filled prior to data removal – john Aug 21 '18 at 15:50
  • So is your code working, but the upload isn't? – Nathan_Sav Aug 21 '18 at 16:00
  • Code works fine, Upload throws corruption errors. I got it to work by saving after sorting, closing the new csv, reopening and then attempting the SpecialCell.ClearContents. Uploads fine with the reopen – john Aug 21 '18 at 16:20

0 Answers0