Deleting rows in Excel

[jandr272]

I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import. I'm a complete noob at VBA but so far I have

Sub ExcelFormat()
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.worbooks.Open ("Z:\Data\Test.xlsx")
 
End Sub

I'm trying to learn this as I go along, and have spent the past few hours reading forums and help sites but I'm not getting a grasp of what the different commands are. Any hints are appreciated.

 

[GinaWhipp]

Hmm, something like...

Const xlShiftUp = -4162
 
Set xlWb = excelApp.worbooks.Open ("Z:\Data\Test.xlsx")
Set xlWs = xlWb.Worksheets(WorksheetNumberHere)
 
Rows("4").Delete xlShiftUp

Added to what you have, not in place of what you have.

 

[KenHigg]

Here's a starting point that may help. Go into excel and turn the macro recorder on. Then perform the row deletes by hand. Save the macro then view the code it generates. Most of the time this is real close to the vba commands you would need to put the MS Aceess code...

 

[jandr272]

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

It is working!

Now just need to figure out how to delete rows based on their contents. The top 7 rows will always get deleted, but I need to delete the first row with nothing in A1 and the 3 rows beneath it.

 

[ino_mart]

Jandr272

You made a common mistake by forgetting to add the object.

excelWS.Rows("1:7").Delete

 

[GinaWhipp]

Try...

Rows("1").Delete

Let's see if it's the Range because you are speciying Row but entering a Range.

 

[jandr272]

Jandr272

You made a common mistake by forgetting to add the object.

excelWS.Rows("1:7").Delete

Figured that out right as you posted it!

Now just need to figure out how to delete rows based on their contents. I need to select the first blank row and delete it and the 3 rows below it.

 

[ino_mart]

If I interpret your question correctly, this should be something like

Sub ExcelFormat()
Dim oXL As Object
Dim wrk As Object
Dim sht As Object
Set oXL = CreateObject("Excel.Application")
Set wrk = oXL.workbooks.Open("c:\temp\file.xls")
oXL.Visible = True
Set sht = wrk.worksheets("testData")
sht.Range("A1").Activate
While Len(oXL.activecell) > 0  'as long there is information in this cell, set pointer one row lower
    oXL.activecell.Offset(0, 1).Activate
Wend
    oXL.Range(oXL.activecell.Offset(0, 0).Address & ":" & oXL.activecell.Offset(0, 3).Address).Delete
End Sub

 

[GinaWhipp]

Thanks ino_mart... I guess I needed more coffee, missed that completely!

 

[jandr272]

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
excelWS.Range("A1").select
[COLOR=red]While Len(excelWS.activecell) > 0[/COLOR]
    excelWS.activecell.offset(0, 1).Activate
Wend
End Sub

I've gotten to here and get run-time error 438: Object doesn't support this property or method. Changed it to

...Set excelWS = excelWB.worksheets("TestData")
excelWS.Rows("1:9").Delete
excelWS.columns("A").Find("").select
End Sub

It is working to select the first row I want to delete, but I don't know how to delete the next two rows as well.

excelWS.activecell.entirerow.delete

Isn't working either, it seems like activecell is not working in anything.

 

[ino_mart]

Hereby the full code which works on my computer

 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
  
'Set the cursor in cell A1
excelWS.range("A1").select
'As long the current selected cell is not empty, move to next row
While Len(excelApp.activecell) > 0
    excelApp.activecell.offset(1, 0).Activate   'Set cursor one row down
Wend
'The current selected cell is empty.
'To retrieve the current rownumber, use activecell.row
'To retrieve the current rownumber + 2, use activecell.offset(2,0).row
'To select these three rows, use range(activecell.row & ":" & activecell.offset(2,0).row)
'To delete these three rows, add .Delete at end of the string
'Do not forget to add the object-variables
'This results in
excelWS.range(excelApp.activecell.row & ":" & excelApp.activecell.offset(2, 0).row).Delete
End Sub

 

[jandr272]

I see where I was mistyping your code, thanks for the clarification it definitely works now. Found a second way to skin this cat too, although I will use yours since it is much more streamlined. Thanks for all the help.

excelWS.columns("A").Find("").select
excelApp.activecell.entirerow.Delete
excelApp.activecell.entirerow.Delete
excelApp.activecell.entirerow.Delete