VBA code to delete rows
 

I have an excel worksheet ("Orders") that is linked to another worksheet
("Master Orders"). What I want to do is create a command button on my
worksheet ("Orders") that will refresh the links as well as delete any rows
on my worksheet ("Orders") that have the word "Closed" in column B. Can
anyone shed some light on how I would write this code?

Thanks
SS

VBA code to delete rows
 

Squirrel

Don't know what "refresh the links" means but for the second part.......

Sub delete_closed()
Dim C As Range
With ThisWorkbook.Sheets("Orders")
With Columns("B")
Do
Set C = .Find("Closed", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If C Is Nothing Then Exit Do
C.EntireRow.Delete
Loop
End With
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Jan 2007 20:34:00 -0800, Secret Squirrel
wrote:

I have an excel worksheet ("Orders") that is linked to another worksheet
("Master Orders"). What I want to do is create a command button on my
worksheet ("Orders") that will refresh the links as well as delete any rows
on my worksheet ("Orders") that have the word "Closed" in column B. Can
anyone shed some light on how I would write this code?

Thanks
SS

VBA code to delete rows
 

Gord,
Thanks for your help!
The "refresh the links" means that I want to update the links that I have to
another file by just pushing a command button. If the data in the other file
is changed and a user has this file open then I want them to be able to click
a button to see if any of the data has changed in the other file. Hope that
makes better sense.

SS

"Gord Dibben" wrote:

Squirrel

Don't know what "refresh the links" means but for the second part.......

Sub delete_closed()
Dim C As Range
With ThisWorkbook.Sheets("Orders")
With Columns("B")
Do
Set C = .Find("Closed", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If C Is Nothing Then Exit Do
C.EntireRow.Delete
Loop
End With
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Jan 2007 20:34:00 -0800, Secret Squirrel
wrote:

I have an excel worksheet ("Orders") that is linked to another worksheet
("Master Orders"). What I want to do is create a command button on my
worksheet ("Orders") that will refresh the links as well as delete any rows
on my worksheet ("Orders") that have the word "Closed" in column B. Can
anyone shed some light on how I would write this code?

Thanks
SS

VBA code to delete rows
 

If the other file is changed AND saved, then you could use this kind of code to
update all the links.

Option Explicit
Private Sub CommandButton1_Click()
With Me.Parent 'thisworkbook
.UpdateLink Name:=.LinkSources
End With
End Sub

I used a commandbutton from the control toolbox toolbar.


Secret Squirrel wrote:

Gord,
Thanks for your help!
The "refresh the links" means that I want to update the links that I have to
another file by just pushing a command button. If the data in the other file
is changed and a user has this file open then I want them to be able to click
a button to see if any of the data has changed in the other file. Hope that
makes better sense.

SS

"Gord Dibben" wrote:

Squirrel

Don't know what "refresh the links" means but for the second part.......

Sub delete_closed()
Dim C As Range
With ThisWorkbook.Sheets("Orders")
With Columns("B")
Do
Set C = .Find("Closed", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If C Is Nothing Then Exit Do
C.EntireRow.Delete
Loop
End With
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Jan 2007 20:34:00 -0800, Secret Squirrel
wrote:

I have an excel worksheet ("Orders") that is linked to another worksheet
("Master Orders"). What I want to do is create a command button on my
worksheet ("Orders") that will refresh the links as well as delete any rows
on my worksheet ("Orders") that have the word "Closed" in column B. Can
anyone shed some light on how I would write this code?

Thanks
SS



--

Dave Peterson