Deleting an entire row in excel

[Danielf]

Hi,

I have in an access form a table with many records. Let say, I have a record with reference "abcde" in field 1. I have also an Excel file with the same references.

I would like when clicking in my access form in field 1 on record "abcde" , that this record would be deleted in my Excel file.

Is this possible?

If yes thanks for help.

Daniel

 

[chergh]

something like

thisworkbook.worksheets("Sheet1").Cells.Find("abcde", LookIn:=xlValues).row.delete

 

[Danielf]

Hi Chergh,

If the name of my Excel file is "myfile.xls" , what is the name of my worksheet ?( I have only one worksheet in this file)

Daniel

 

[chergh]

Are you honestly saying you don't know how to find the name of a worksheet in excel

Open the file and at the bottom of the worksheet is a tab with the name of the worksheet on it.

 

[Danielf]

Are you honestly saying you don't know how to find the name of a worksheet in excel

Open the file and at the bottom of the worksheet is a tab with the name of the worksheet on it.

Of course not, but in this file, there is no tab at the bottom with name "Sheet1 Sheet 2 ....."

 

[chergh]

In that case use the ordinal position, which I believe is 1 based.

So use

worksheets(1)

If you really want to know the name use

debug.print thisworkbook.worksheets(1).name

 

[Danielf]

In that case use the ordinal position, which I believe is 1 based.

So use

worksheets(1)

If you really want to know the name use

debug.print thisworkbook.worksheets(1).name

Thanks, I have tried and I am receiving the name "Singles"

Daniel

 

[Danielf]

something like

thisworkbook.worksheets("Sheet1").Cells.Find("abcde", LookIn:=xlValues).row.delete

Hi Chergh,

What do you mean by "xlValues" ?

Daniel

 

[boblarson]

xlValues is an Excel CONSTANT. You use it as shown, no changing of the name or anything.

 

[Danielf]

xlValues is an Excel CONSTANT. You use it as shown, no changing of the name or anything.

Thanks for your reply but using the code as above , I am receiving the following error : "Compile error, Invalid qualifier" for ".Row "

Daniel

 

[Danielf]

I have found the solution: I have replaced Row by EntireRow and it works.

Daniel

 

[Brianwarnock]

The problem with that solution is that it aborts if abcde is not found, if that is a possibility then you need

Set c = ThisWorkbook.Worksheets("Sheet1").Cells.Find("abcde", LookIn:=xlValues)
If c Is Nothing Then Exit Sub
a = c.Address
ThisWorkbook.Worksheets("Sheet1").Range(a).EntireRow.Delete

Brian

 

[Danielf]

The problem with that solution is that it aborts if abcde is not found, if that is a possibility then you need

Set c = ThisWorkbook.Worksheets("Sheet1").Cells.Find("abcde", LookIn:=xlValues)
If c Is Nothing Then Exit Sub
a = c.Address
ThisWorkbook.Worksheets("Sheet1").Range(a).EntireRow.Delete

Brian

Hi Brian,

Thanks for your reply, I will try it.
Two other questions:

When, I am running the code, It works and closes the Spreadsheet, but Excel is still open . Is it possible to close not only the file but also Excel?

This is the code that I have written:

xlwrkBk.Close 'Close the spreadsheet object
Set xlSheet = Nothing 'Delete the worksheet object
Set xlwrkBk = Nothing 'Delete the spreadsheet object
xlApp.Quit 'Close the excel application object
Set xlApp = Nothing 'Delete the application object

Is it possible to open an Excel file without maximize it? I know that with an Access form, you can use DoCmd.MoveSize ,, but how does it work with Excel?.

Daniel

 

[Brianwarnock]

To close Excel

Application.Quit


I don't know how to alter the screen size other than make it full screen which you wouldn't want.

Brian

In a module of your workbook create a Sub Autpen() with one of the following
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal

 

[Danielf]

Hi Brian,

Thanks for your reply.

Instead of alter the screen size, is it possible to go - when opening the spreadheet- with the cursor to a certain cell ? ( like in access with the command DoCmd.GoToRecord....) ?

 

[Danielf]

Hi Brian,

Thanks for your reply.

Instead of alter the screen size, is it possible to go - when opening the spreadheet- with the cursor to a certain cell ? ( like in access with the command DoCmd.GoToRecord....) ?

I have found it :

Just replacing entireRow.delete by select


Set c = ThisWorkbook.Worksheets("Sheet1").Cells.Find("abcde", LookIn:=xlValues)
If c Is Nothing Then Exit Sub
a = c.Address
ThisWorkbook.Worksheets("Sheet1").Range(a).select