Clear Specific Range

[Joseph]

I currently have a spefic sheet that contains all my source data for a workbook.

The first two colums A & B are "Employee Name and Group". The form I'm working on manages my employee roster. I want the ability to "remove" an employee from my list. When I run this code, it works, however it clears the whole row. How can I re-write this so only data from column A & B are cleared based on the selection.

Code: Select all

Private Sub CommandButton2_Click()
Set ws = Worksheets("Data")

'Find Employee info
   iRow = ws.Columns("A:A").Find(Me.List_employee.Text).Select


'Clear Employee name and Group
     Selection.EntireRow.Value = ""

Thanks, figure this is a pretty easy one.

[HansV]

Change

Code: Select all

'Find Employee info
   iRow = ws.Columns("A:A").Find(Me.List_employee.Text).Select

'Clear Employee name and Group
     Selection.EntireRow.Value = ""

to

Code: Select all

'Find Employee info
    iRow = ws.Columns("A:A").Find(Me.List_employee.Text).Select

'Clear Employee name and Group
    ws.Range("A" & iRow & ":B" & iRow).ClearContents

[Joseph]

Getting and Object or Application Error.

Added the workbook...

[Joseph]

Sorry for the double...figured it out.

Selection(1, 1).ClearContents
Selection(1, 2).ClearContents

[HansV]

Sorry, should have seen that.

iRow = ws.Columns("A:A").Find(Me.List_employee.Text).Select

should have been

iRow = ws.Columns("A:A").Find(Me.List_employee.Text).Row

In most situations, it isn't necessary to select a range of cells to manipulate it.

[Joseph]

Ah-Ha...Thanks Hans!!

I'm trying now to have VBA assign a primary key for each new record in order....would you know of any code or resources to do that?

[HansV]

In what sense? Do you want a sequence number, or...?

[Joseph]

Yes. Essentialy the user will enter data into the form. When the data is "Inputed" to the Excel sheet, I have a column (E) labeled (Project Number), I want a unique sequential number to be generated for each row of data entered.

Similar to the Access Primary Key.
1
2
3
4
5.

I thought while typing this, perhaps a formula similar to, seems to work actually.
=IF(F3="","",E2+1)

[HansV]

The disadvantage of a formula is that if you sort the data, the numbers will get messed up. If you use VBA to add a new record (row), you can assign a new number at the same time.
Let's say you add a record to the row indicated by the variable lngRow. The following line will calculate the maximum of the cells in column E above that row, increase it by 1 and enter the value in column E:

Range("E" & lngRow) = Application.WorksheetFunction.Max(Range("E2:E" & (lngRow - 1))) + 1

[Joseph]

Beautiful...since I had already had the variable irow...I just changed to

Range("E" & iRow) = Application.WorksheetFunction.Max(Range("E2:E" & (iRow - 1))) + 1

Is good? Works.

Thanks Hans!!!

[HansV]

This will work even if the rows have been sorted on another column than column E.