clearing a range of cells in a row

Question

hi guys,

is there a way to select a range of cells in a worksheet and clear the data in them?

i DO NOT want the WHOLE ROW to be deleted, only a few selected cells (they are all adjacent to each other)

and i don't think the ActiveSheet.ranges method works as:
1) the coordinates of the cells in the row to be cleared are determined logically by a VBA sub, i know how to get the coordinates, just dunno how to delete the row
2) this needs to be applied in different areas of the worksheet AND across OTHER worksheets as well.

I'm quite sure I have the logic needed to find the coordinates just need to know how to clear the cells

if there is a way to do it that is similar to ActiveSheet.cells ( row, column ) then it would be best

from my understanding the ranges method only takes in letter and number formats in strings... i need something that takes in number and number


thank you!

Answer 1

update: it should also not remove the colors and conditional formatting in the cells, and the table outline, just the data in the cell

Answer 2

Both Range and Cell objects have .ClearContents() method

Answer 3

In regards to the cell format the following converts a number to a column letter which would allow you to try the method cicatrixx suggested.

Dim Numbers As List(Of Integer) = Enumerable.Range(1, 30).ToList
For Each number In Numbers
    Console.WriteLine("[{0}] [{1}]", number, number.ExcelColumnName)
Next

Code module

Public Module ExcelExtensions
   <System.Runtime.CompilerServices.Extension()> _
   Public Function ExcelColumnName(ByVal Index As Integer) As String
      Dim chars = New Char() _
          { _
              "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, _
              "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
              "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c _
          }
      Index -= 1
      Dim columnName As String
      Dim quotient = Index \ 26
      If quotient > 0 Then
         columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
      Else
         columnName = chars(Index Mod 26).ToString()
      End If
      Return columnName
   End Function
End Module

---

KSG