dividing numbers & blank rows

[wakwak1]

Dim x As Integer

Sheets("Sheet2").Select
Range("K3").Select

Do While IsEmpty(ActiveCell.Offset(0, 0)) = False

x = ActiveCell.FormulaR1C1
x = x / 100
ActiveCell.FormulaR1C1 = x

ActiveCell.Offset(1, 0).Select

However, when the active cell has like a few decimal places, then when I divide by 100 it ends up giving me an integer (i still want decimal places!).
I tried declaring x as long etc but that did not help.

2. I have certain rows of data (J3:M3) and the rows go from 3,4,5... etc and it is not known at what row they stop. If I have blank rows...how can i get vba to move the next available row of data up...so that there are no blank rows?

[anandbohra]

try this it will divide all the values with 100 which u selected in inputbox


Sub div_by_100()
Dim selrange As Range
Dim xval
Set selrange = Application.InputBox("Select Data range", , Selection.Address, , , , , 8)
For Each xval In selrange
xval.Value = xval / 100
Next xval
End Sub

[mikerickson]

Both Long and Integer only take mathematical integer {..,-2,-1,0,1,2,..}) values.

Declare x as Double and it will work right.

Single is seldom used, but will also handle fractional values.


Range("J65536").End(xlUp) will return the last filled cell in column J.

[wakwak1]

I'm not quite clear on the "Range("J65536").End(xlUp) will return the last filled cell in column J." part.


Can you elaborate on that plz.

[Bob Phillips]

It means that it does a search up from the bottom row untiil it finds a non-blank cell. So it doesn't matter about blank rows before the last filled cell.

[wakwak1]

ok but how can i work that into some code which will automatically get rid of the blank rows

[Bob Phillips]

Dim rng As Range

With ActiveSheet

Set rng = .Range(.Range("J1"), .Range("J" & .Rows.Count).End(xlUp))
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With