mexmike
08-22-2015, 07:15 PM
Hi All,
I have a worksheet that contains rows of data, with formulas in certain columns.
I am trying to use a userform combobox to selct from a list of names in the sheet, one name per sheet row with formulas on either side.
The names begin at row 6 and column "E". Row 5 is a header.
I need to be able to clear the contents of the row that contains the selected name without deleting the formulas in that row or anywhere else on the sheet.
I have found the following code and have been trying to adapt it to work with the combobox to no avail.
Public Sub Main_KeepFormulas()
Dim srow, lCol As Integer
Dim cell As Variant
srow = ActiveCell.row
lCol = cells(srow, Columns.Count).End(xlToLeft).Column
For Each cell In Range(cells(srow, 1), cells(srow, lCol))
If cell.HasFormula = False Then cell.ClearContents
Next cell
End Sub
I also need to be able to repopulate the vacant row without affecting the formulas (prefer not to shift up).
Any ideas :dunno
I have a worksheet that contains rows of data, with formulas in certain columns.
I am trying to use a userform combobox to selct from a list of names in the sheet, one name per sheet row with formulas on either side.
The names begin at row 6 and column "E". Row 5 is a header.
I need to be able to clear the contents of the row that contains the selected name without deleting the formulas in that row or anywhere else on the sheet.
I have found the following code and have been trying to adapt it to work with the combobox to no avail.
Public Sub Main_KeepFormulas()
Dim srow, lCol As Integer
Dim cell As Variant
srow = ActiveCell.row
lCol = cells(srow, Columns.Count).End(xlToLeft).Column
For Each cell In Range(cells(srow, 1), cells(srow, lCol))
If cell.HasFormula = False Then cell.ClearContents
Next cell
End Sub
I also need to be able to repopulate the vacant row without affecting the formulas (prefer not to shift up).
Any ideas :dunno