Skip to main content

deleting entire rows after collecting the data

  • March 6, 2018
  • 5 replies
  • 0 views
D

i was wondering if anyone can show me a code which deletes entire rows in excel which also moves everything up. so line 3 becomes line 2 and line 4 becomes line 3 an so on. i can't really figure out how to do it with micro focus rumba 9.3 script editor. any help that i can get is appreciated


#Rumba

5 replies

D

i was wondering if anyone can show me a code which deletes entire rows in excel which also moves everything up. so line 3 becomes line 2 and line 4 becomes line 3 an so on. i can't really figure out how to do it with micro focus rumba 9.3 script editor. any help that i can get is appreciated


#Rumba
Hi besafe,

here is some sample code to demonstrate how to do this.

Here I remove rows 2 and 3 (starting from the the bottom up, or you may remove the incorrect row by accident).

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Sub Main

Dim XLApp as Object
Dim MyBook as Object
Dim mySheet as Object

Set XLApp = CreateObject("Excel.Application") ' Create an EXCEL application
XLApp.visible = true
Set MyBook = XLApp.workbooks.add

Set MySheet = MYBook.Worksheets("Sheet1")
MySheet.Activate ' Activate the Worksheet


For r = 1 To 10
For c = 1 To 10
XLApp.Cells(r, c).Value = cstr$(r) & ", " & Cstr$(c)
Next c
Next r
Sleep 2000
MySheet.Rows(3).EntireRow.Delete
Msgbox "Row 3, removed entire row", 0, "Remove row sample"
Sleep 2000
MySheet.Rows(2).EntireRow.Delete
Msgbox "Row 2, removed entire row", 0, "Remove row sample"
Sleep 1000

End Sub
D

i was wondering if anyone can show me a code which deletes entire rows in excel which also moves everything up. so line 3 becomes line 2 and line 4 becomes line 3 an so on. i can't really figure out how to do it with micro focus rumba 9.3 script editor. any help that i can get is appreciated


#Rumba
Hi besafe,

here is some sample code to demonstrate how to do this.

Here I remove rows 2 and 3 (starting from the the bottom up, or you may remove the incorrect row by accident).

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
Sub Main

Dim XLApp as Object
Dim MyBook as Object
Dim mySheet as Object

Set XLApp = CreateObject("Excel.Application") ' Create an EXCEL application
XLApp.visible = true
Set MyBook = XLApp.workbooks.add

Set MySheet = MYBook.Worksheets("Sheet1")
MySheet.Activate ' Activate the Worksheet


For r = 1 To 10
For c = 1 To 10
XLApp.Cells(r, c).Value = cstr$(r) & ", " & Cstr$(c)
Next c
Next r
Sleep 2000
MySheet.Rows(3).EntireRow.Delete
Msgbox "Row 3, removed entire row", 0, "Remove row sample"
Sleep 2000
MySheet.Rows(2).EntireRow.Delete
Msgbox "Row 2, removed entire row", 0, "Remove row sample"
Sleep 1000

End Sub
D

i was wondering if anyone can show me a code which deletes entire rows in excel which also moves everything up. so line 3 becomes line 2 and line 4 becomes line 3 an so on. i can't really figure out how to do it with micro focus rumba 9.3 script editor. any help that i can get is appreciated


#Rumba
big thanks, but im really new to this scripting, can you maybe explain some of the steps
D

i was wondering if anyone can show me a code which deletes entire rows in excel which also moves everything up. so line 3 becomes line 2 and line 4 becomes line 3 an so on. i can't really figure out how to do it with micro focus rumba 9.3 script editor. any help that i can get is appreciated


#Rumba
Hi besafe,

on the important bits..

'launch an instance of MS Excel
Set XLApp = CreateObject("Excel.Application") ' Create an EXCEL application
'Make it visible
XLApp.visible = true
'add a workbook
Set MyBook = XLApp.workbooks.add

'activate the worksheet
Set MySheet = MYBook.Worksheets("Sheet1")
MySheet.Activate ' Activate the Worksheet

'populate rows 1 to 10 and columns 1 to 10 with some data (in this case the row, col info).
For r = 1 To 10
For c = 1 To 10
XLApp.Cells(r, c).Value = cstr$(r) & ", " & Cstr$(c)
Next c
Next r
'pause for 2 seconds so you can see the entire data
Sleep 2000
'delete row 3
MySheet.Rows(3).EntireRow.Delete
Msgbox "Row 3, removed entire row", 0, "Remove row sample"
'pause for 2 seconds so you can see your old row 3 was remove
Sleep 2000
'delete row 2
MySheet.Rows(2).EntireRow.Delete
Msgbox "Row 2, removed entire row", 0, "Remove row sample"
'Now you should see that the original rows 2 and 3 were removed
D

i was wondering if anyone can show me a code which deletes entire rows in excel which also moves everything up. so line 3 becomes line 2 and line 4 becomes line 3 an so on. i can't really figure out how to do it with micro focus rumba 9.3 script editor. any help that i can get is appreciated


#Rumba
thank you so much, it works perfectly now :D

Recent badge winners

Explore all badges
Terms & ConditionsAccessibility statement