I'm using a module to clear cells but I need to keep the formulas 2

[coolcarls]

I'm using this formula to clear a range of cells (amongst other things)

Application.ScreenUpdating = False
Application.Goto Reference:="Database"
nextblank = [A65536].End(xlUp).Offset(1, 0).Address
Range(nextblank).Select
Range("Input&quot.Copy
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Application.Goto Reference:="Input"
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
Range("Input&quot.ClearContents
Application.ScreenUpdating = True

Is there a way that I can keep the formulas in the cells intact and just clear the text, I tried this

Sheet2.Range("K4:K23&quot.Text = ""

but that gives me an error. Oh what to do

 

[Loomah]

Hi coolcarls
This is a piece of code I basically made up a while ago while trying to workout how to find the TRUE used range on a worksheet.

I've never understood what it does exactly as it may have a strange way of selecting cells! However, purely by cance, I discovered that it doesn't select formulae. Spooky.

Anyway, give it a go. It may not be what your looking for at all but I'd appreciate any feedback.

Sub used_Range()
Dim lRow As Long
Dim iCol As Integer
Dim iAreas As Integer
With ActiveSheet.Range("A1&quot.SpecialCells(xlEnd)
.Select
lRow = .Row 'or use rows.count
iCol = .Column 'or use columns.count
End With
Debug.Print lRow
Debug.Print iCol
Debug.Print Selection.Areas.Count
' selection.clearcontents 'or something
' HANDLE WITH CARE!!
End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Loomah - that's a work of f*&$%^n genius

Can't seem to re-create it manually tho - Special Cells Last just goes to that cell and holding down shift or ctrl just selects the whole area - any ideas 'cos I'm very curious as to exactly what it's doing....
Have a star from me anyway 'cos this is USEFUL
HTH
~Geoff~

 

[Loomah]

xlbo
I'm completely stumped as to what it does, and I've asked a couple of times if anyone else does on these very pages!

Watch what it does. I tested/created using a range something like A15 & G8 for example. I then added a sum calc somewhere.

The stangest bit is when I removed contents from B3. The way the cells were selected was well strange - hence the row, column and area counts to see if it made sense. Row and column counts (if I remember correctly) are totally illogical.

Have a play and see if you can understand any of it!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Loomah - think I've got an answer. Here's a reply from an excel mailing list that I'm part of:

I think what you are finding is actually the same as .SpecialCells(
xlCellTypeConstants)

If you lookin the object browser, you will find the the constants
xlCellTypeConstants and xlEnd both evaluate to 2

Looks like excel is treating xlEnd as the same as xlConstants - this would definitely explain the non selection of formulae and, manually, when I select constants, it gives the same result. The .Rows and .Cols give inaccurate answers because they refer only to ONE of the areas in the range.

Have just tested with xlCellTypeConstants substituted for xlEnd and got exactly the same answers - Looks like we got an explanation !!
Emoticon Overkill
HTH
~Geoff~

 

[coolcarls]

That's incredible! How the heck d'ya come up with that??
It seems to me (newbee) to act odd, but I am often left feeling that way with this stuff. I can't get it to do exactly what I am looking for and I'm sure with more knowledge and/or tweaking I could, but thanks...A lot

 

[coolcarls]

I got it to work!!!!!!!!!!!!!!!!!!!HA
who da man????????
you da man!!!!!!!
thanks Loomah