Microsoft Office Tutorials and References
In Depth Information
Deleting the Contents of Unlocked Cells
'To delete the cell, use¶
'Cll.Delete Shift:=xlShiftUp¶
End If¶
Next Cll¶
End Sub¶
' * * * * *¶
Sub UniqueList(ByVal Column1 As Range, ByVal Column2 As Range, _¶
RngDest As Range)¶
Dim WS As Worksheet¶
'Use a temporary worksheet to use Advanced Filter on it¶
Set WS = Workbooks.Add(xlWorksheet).Worksheets(1)¶
'Put the first column¶
WS.Range("A1").Resize(Column1.Rows.Count).Value = Column1.Value¶
'Put the second column, skip one row, which is¶
'the heading¶
WS.Range("A1").Offset(Column1.Rows.Count).Resize( _¶
Column2.Rows.Count - 1).Value = Column2.Offset(1).Resize( _¶
Column2.Rows.Count - 1).Value¶
'Now, use advanced filter and put the results directly in¶
'the destination range¶
WS.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _¶
CopyToRange:=RngDest, Unique:=True¶
'Close the temp workbook without saving¶
WS.Parent.Close SaveChanges:=False¶
End Sub¶
Deleting the Contents of Unlocked Cells
This macro illustrates how to delete only those cells that do not contain
formulas and are unlocked, which means that they are entry cells.
Example file:
Scenario: When using a workbook that has multiple sheets
that all contain input (unlocked, so the user can enter data in
them) and output cells (locked, so the user can only see the
results and cannot change the formula inside them), you
often need to clear only the input cells.
Suppose you take complaint calls and, using Excel, you fill
out a form, print it, and don’t save it because the printed
copy is your only required record. Rather than using a
template each time, or closing the file without saving, you
can now put a command button on a worksheet that, when
clicked, erases the data you entered, thus providing you with
a new, blank form.
Search JabSto ::

Custom Search