[RESOLVED] delete rows in excel form using vb

[wizkid]

hi,
i want to delete entire rows from the excel form before writing new rows to the excel form.
is it possible.
thanks

[DKenny]

hi,
i want to delete entire rows from the excel form before writing new rows to the excel form.
is it possible.
thanks

When you say form are you really talking about a worksheet?

[wizkid]

sorry,
i am working on worksheet
i want to delete the entire rows of the sheet
thanks

[DKenny]

The attached code will delete the entire row based on a single cell. It should be enough to get you started

Sub SampleDelete()
Dim rngSingleCell As Range
    
    Set rngSingleCell = ThisWorkbook.Worksheets(1).Range("c12")
    
    rngSingleCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

[wizkid]

thanks a lot
i checked this one with sample code.
i will continue with this.it is working
but i got some other problem

i am getting all the values in worksheet.
but here i am getting some error

Application.Workbooks("Book1.xls").Save ...in this line

like subscript out of range
why this happens

[wizkid]

i can't delete the entire rows
some are still there.
any suggestions

[DKenny]

Can you post your code?

[wizkid]

i want to put so manyvalues in work sheets
c11 means here ??
i want to delete each and every row

Set oWB = moApp.Workbooks.Open("C:\Book1.xls")
     oWB.Sheets(1).Activate
    Dim rngSingleCell As Range
   
    Set rngSingleCell = moApp.Worksheets(1).Range("c11")
 
 
i = 2
For qq = 0 To p - 1
  
       oWB.Sheets(1).Cells(i, 1).Value = divide_price_list(qq)
        i = i + 1
       
 Next
i = 2  For qq = 0 To p - 1
      calvalues (divide_price_list(qq)) 'this is function
       
        oWB.Sheets(1).Cells(i, 2).Value = grosssales_rs(1)
        i = i + 1
        grosssales_rs.Close
      
 Next

[DKenny]

i want to put so manyvalues in work sheets
c11 means here ??
i want to delete each and every row

Ok, now I'm confused. Do you want the procedure to add data to the sheet or to delete every row. Please take some time to explain exactly what you want this procedure to achieve.

[wizkid]

i am getting some value from the procedure,
and i want to put the procedure returned value in one of the excel worksheet cell.
like i want to put each and every value(which is returned by the procedure) in the sheet

before that .,i want to erase the entire worksheet before running the program.
here i want only one thing how to erase entire contents of the worksheet after open the worksheet.

Set oWB = moApp.Workbooks.Open("C:\Book1.xls")
     oWB.Sheets(1).Activate
    Dim rngSingleCell As Range
   
'''''here my program starts....
so i want to erase the entire rows in book.xls
 
i = 2
For qq = 0 To p - 1
  
       oWB.Sheets(1).Cells(i, 1).Value = divide_price_list(qq)
        i = i + 1
       
 Next
i = 2  For qq = 0 To p - 1
      calvalues (divide_price_list(qq)) 'this is function
       
        oWB.Sheets(1).Cells(i, 2).Value = grosssales_rs(1)
        i = i + 1
        grosssales_rs.Close
      
 Next

if it is not clear then please tell me

[DKenny]

here i want only one thing how to erase entire contents of the worksheet after open the worksheet.

To erase the entire contents of the worksheet, which keeping any cell formatting, use the following syntax.

Worksheets("YourSheetNameHere").Cells.ClearContents

You will need to change "YourSheetNameHere" to the name of the sheet that you want to clear

If you want to clear the formatting as well as the data, then use the following syntax.

Worksheets("YourSheetNameHere").Cells.Clear

[wizkid]

thank you very much dKenny
it is working well.
but some times when i open the report first time and i am saving the report using vb code i am getting somw error like,subscript out of range
what is reason??
wizkid

[DKenny]

Good.

Don't forget to mark the Thread as resolved.

[wizkid]

sure