UserForm Delete Sheet Columns

H

Hazel

Hi

I have a simple Userform that will add or delete data within 2 sheets named
Sheet1 & Sheet2
Using the sub below it will delete the Name from Col A of Sheet1 OK.
Sheet2 retains the input data in 2 columns starting at Col D. Row 1 is the
header row and is formatted as this example Row1 - Cols D & E merged and the
Name is across these 2 Cols the data starts on Row2 Cols D & E there are 20
Names & Cols formatted this way and it could be as many as 40. Is there a way
when running the sub below that it will also delete the Name and the Columns
in Sheet2 associated with the selection of Combox1 and Sheet1.

Private Sub Remove_Click()
If MsgBox("Are You Sure You Want To Delete This Name If So Click Yes",
vbYesNo) = vbNo Then
Exit Sub
Else
End If
Sheets("Sheet1").Rows(Combox1.ListIndex + 1).Delete Shift:=xlUp
End Sub

Any help much appreciated
 
G

GS

Hazel explained :
Hi

I have a simple Userform that will add or delete data within 2 sheets named
Sheet1 & Sheet2
Using the sub below it will delete the Name from Col A of Sheet1 OK.
Sheet2 retains the input data in 2 columns starting at Col D. Row 1 is the
header row and is formatted as this example Row1 - Cols D & E merged and the
Name is across these 2 Cols the data starts on Row2 Cols D & E there are 20
Names & Cols formatted this way and it could be as many as 40. Is there a way
when running the sub below that it will also delete the Name and the Columns
in Sheet2 associated with the selection of Combox1 and Sheet1.

Private Sub Remove_Click()
If MsgBox("Are You Sure You Want To Delete This Name If So Click Yes",
vbYesNo) = vbNo Then
Exit Sub
Else
End If
Sheets("Sheet1").Rows(Combox1.ListIndex + 1).Delete Shift:=xlUp
End Sub

Any help much appreciated

If you put the name from Sheet1 into a string variable, you can use the
Find() function on Sheet2 to get the row to delete there.

Also, if you include .EntireRow before .Delete you won't need to
specify Shift when deleting rows.

Garry
 
H

Hazel

--
Many Thanks


GS said:
Hazel explained :

If you put the name from Sheet1 into a string variable, you can use the
Find() function on Sheet2 to get the row to delete there.

Also, if you include .EntireRow before .Delete you won't need to
specify Shift when deleting rows.

Garry
Hi Gary

I understand the explanation for deleting the Entire Row - however I
wouldn't have a clue about Find Function and String Variable and how to
insert such a Function within the Sub - can you help with this?

Many Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top