PDA

View Full Version : [SOLVED] Clear Sheet Row Containing Combobox Selection without Affecting Formulas in Row



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

p45cal
08-23-2015, 05:34 AM
you need something like
Range(Cells(activecell.row, "E"), Cells(ActiveCell.Row, "H")).SpecialCells(xlCellTypeConstants, 23).ClearContents

There are many different ways of specifying the range from which you want to delete non-formula cells, Range(Cells(activerow, "B"), Cells(ActiveCell.Row, "H")) is just one of them, there'll probably be a better one for your situation.
If there are all formulae in the range this will fall over so you could put an on error resume next above the line and an on error goto 0 below it.

mexmike
08-24-2015, 09:12 AM
Hi and thanks for the input.

I'm getting a Run-time error '1004' Method 'Range' of object'_Global' failed on the range selection line. I changed 'activerow' which I thought should have been "ActiveCell.Row", but then get the error mentioned

mexmike
08-24-2015, 02:29 PM
Looks like I've figured it out.



Dim cell As Range
Dim lCurrentRow As Long
Dim lCurrentCol As Long
Range("E6").Select 'First row after header that contains Combobox search data
cells.Find(what:=ComboBox_Remove.Value, After:=ActiveCell, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Activate

lCurrentRow = ActiveCell.row
lCurrentCol = ActiveCell.Offset(0, 4).Column 'Last column with data

With cells

Range(cells(lCurrentRow, 1), cells(lCurrentRow, lCurrentCol)).Select

For Each cell In Range(cells(lCurrentRow, 1), cells(lCurrentRow, lCurrentCol))
If cell.HasFormula = False Then cell.ClearContents
Next cell
End With



As I don't want to shift rows up. I now need to be able to populate the vacant cells in the same manner without overwriting the formulas.

So my next question is how to achieve finding the row with cleared data and writing new data into the blanks:think: