0

I am working on a module that will format an Excel spreadsheet for import into Access. The spreadsheet comes from the source with 7 rows of header data, the data I need, and 4 rows of jibberish below the data. So far I have gotten through deleting the header info:

Sub ExcelFormat()
Dim excelApp As Object
Dim excelWB As Object
Dim excelWS As Object
Set excelApp = CreateObject("Excel.Application")
Set excelWB = excelApp.workbooks.Open("Z:\Data\Test.xlsx")
excelApp.screenupdating = True
excelApp.Visible = True
Set excelWS = excelWB.worksheets("TestData")
excelWS.Rows("1:7").Delete

I am having trouble selecting the first blank cell in A and deleting it and the 4 rows beneath it. Thanks in advance.

Erik A
  • 31,639
  • 12
  • 42
  • 67
user3779912
  • 1
  • 1

2 Answers2

0

Add this:

 dim i as long

 'loop through every row in the used range
 for i = 1 to excelWS.usedrange.rows.count

      'test if the cell in column A of the current row2 is blank
      If excelWS.cells(i,1)="" then

           'if the cell is blank, delete the row and the 4 rows beneath it, starting with the bottom most row
           excelWS.rows(i+4).delete
           excelWS.rows(i+3).delete
           excelWS.rows(i+2).delete
           excelWS.rows(i+1).delete
           excelWS.rows(i).delete

           'exit the loop
           Exit for

      end if

 next

This will start in row 1 and go through every cell in A until it finds a blank cell. Then it will delete the row and the 4 rows beneath it and stop.

If you simply want to delete the last 5 rows of data:

 dim i as long

 i = excelWS.usedrange.rows.count

 excelWS.usedrange.rows(i & ":" & i-4).delete
asp8811
  • 793
  • 8
  • 14
0

First delete the last 4 rows and after that the header;

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(LastRow - 4 & ":" & LastRow).Delete
.Rows("1:7").Delete
End With
Kokkie
  • 546
  • 6
  • 15