Add a 'go to next cell down' line in macro

[oliviar]

Hi guys,
I have a simple macro:

Sub CellValue_Add_1()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

I would like it to automatically move down to the next visible line (its autofiltered, so it can't go to the next hidden line, incase one is there).

Alternatively, I would love my macro to add 1 to all selected visible cells at once - if that is possible.

 

[Boller]

Assume that the filter is in Column A and row 1 is a header row :-

Dim cel As Range, rng As Range
Set cel = Cells(Rows.Count, 1)
Set rng = Range([A2], cel.End(xlUp)).SpecialCells(xlCellTypeVisible)
cel = 1
cel.Copy
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
cel.EntireRow.Delete
ActiveSheet.UsedRange

 

[oliviar]

Hi Boller,
This looks impressive, but I'm really sorry, I don't know enough VB to be able to know what its saying!
Is this a macro which I add to the one I have, or is it the macro that will add 1 to everything I select in one hit?

Sorry

 

[Boller]

It is a macro that will add 1 to each visible cell in Column A starting from A2 to the last visible cell within the filter range. (It has been assumed that all the column A cells will contain numerical data only.)

If you want it to add 1 to selected cells only, it needs to be re-written.

 

[oliviar]

That is awesome.
I added "Sub add1()" to the beginning
and 'end sub' at the bottom, like the other macros seem to have.
It seems to work now.

Is that what I was supposed to do?

 

[oliviar]

oh no!
It killed my sheet!!!
I told it I wanted 1 added to column I, but changing the A to an I
But now its trying to add 1 to EVERY column in my table - even the ones that aren't visible.

Why?

 

[Boller]

In addition to changing the A to an I, you need to change this :-

Set cel = Cells(Rows.Count, 1)

to this :

Set cel = Cells(Rows.Count, 9)

 

[oliviar]

I'm missing data. Is it deleting anything?

 

[Boller]

I'm missing data. Is it deleting anything?

No.

 

[Boller]

Here's an explanation of what each line does :-

'Define variables
Dim cel As Range, rng As Range

'Assign the last cell of the sheet in column 1 to the variable "cel"
Set cel = Cells(Rows.Count, 1)

'Set the visible cells in column A from A2 to the last cell in the filter range
Set rng = Range([A2], cel.End(xlUp)).SpecialCells(xlCellTypeVisible)

'Put the value "1" in cel
cel = 1

'Copy the value of cel
cel.Copy

'Add the value of cel to rng
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd

'Delete the sheet's last row (which contains cel)
cel.EntireRow.Delete

'Reset the sheet's last used cell
ActiveSheet.UsedRange