Two basic Excel VBA questions

[Lou_Reed]

In the attached zipped file is a Excel spreadsheet with two form control buttons on it.



The second button, addcolumnremoverows has a three lines of VBA code that should put numbers in that column
starting with 1 on the second row. Now starts off on the second row but with 2. I would rather it be 1.
how is this code modified to do it?

Also, what is the VBA code to select all cells in an Excel worksheet? I read somewhere that
is as simple as cells.select. That would be great if it were true. Is it?

cells.select

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[JonSmile]

For the first bit you could do a simple
Range ("A" & X).Value = X-1

[Lou_Reed]

Okay, but what is the VBA code that I that will select all of the Excel spreadsheet's cells?


Any help appreciated. Thanks in advance.


Respectfully,


Lou Reed

[Lou_Reed]

Okay, the line in the VBA code

Sheet1.Cells.Select

will do it. However, when that happens it gets into the Excel VBA get bogged down and seems to get intro an infinite loop. I have to shut own Excel to get things back to normal.

How do I stop this?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[JonSmile]

When you say "all cells in a spreadsheet".
Do you really mean ALL cells (a1 - zzz999)
or the cells in a range where you have entered data (so a1 - position of last active cell)?

Sub SelectAll()
cells.select in VBA
End Sub
does the 1st.

Sub SelectYourRange()
Range(Cells(1, 1), ActiveCell).Select
End Sub

does the 2nd

[Lou_Reed]

I mean all cells that have a value in them. Blank cells are ignored.

Respectfully,

Lou Reed

[JonSmile]

Try this

Sub jon01()


Set Rng =Range(Cells(1, 1), ActiveCell)


'Selecting only hardcoded data
Rng.SpecialCells(xlCellTypeConstants).Select
End Sub

[Lou_Reed]

I think that manually selecting all cells in a big spreadsheet based on whether they have a number in them can be reckless. Someone may not get all of them. Then we get errors.

If I can I write a VBA program that will only select cells that actually have a value in them, that would be much better. Can your VBA code posted immediately above (post # 7) do this?

Respectfully,

Lou Reed

[JonSmile]

Hi,

The code below is the quickest and as far as I know the easiest way to automatically select all cells with data in them.

There is no manual selection.

Did you even try it before posting the above comment?

Sub jon01()


Set Rng =Range(Cells(1, 1), ActiveCell)


'Selecting only hardcoded data
Rng.SpecialCells(xlCellTypeConstants).Select
End Sub

[Lou_Reed]

No, I am ashamed to admit that i never did try it before posting. I am not sure that I would have known if it worked. The spreadsheet is huge and inspecting all cells is not a good idea - errors will occur.

Respectfully,

Lou Reed

[Lou_Reed]

In the Post # 9 if I compile that it will say that Rng is not defined.

I must define it as something. Please what do I define it as? I know that it is an easy VBA question,but not for me.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

Lou,

Looking at the code below, what object type do you think "Rng" should be declared as???

Sub jon01()
   Dim Rng As ????

   Set Rng = Range(Cells(1, 1), ActiveCell)

   'Selecting only hardcoded data
   Rng.SpecialCells(xlCellTypeConstants).Select

   Set Rng = Nothing
End Sub

[Lou_Reed]

I must apologize for my carelessness (stupidity). Rng should be declared as a Range. It is just not what i amused to. It seems I usually declare integers, real,and things like that. I have only come across declaring range in VBA for Excel.

Respectfully,

Lou Reed