Hi,
I want to write a macro which checks the end of worksheet in VBA. I wanted
to know are there any constants or function which identifies End of Active
Sheet like EOF as End of File.

--
Chanikya

See replies to similar query he

http://tinyurl.com/b2ajf

Hope this helps
Rowan

cndesu wrote:
Hi,
I want to write a macro which checks the end of worksheet in VBA. I wanted
to know are there any constants or function which identifies End of Active
Sheet like EOF as End of File.

Hi,
Thanks for the reply. Already I got the solution right away. But the problm
for which I has asked is explained in detail as follows.
I wrote a macro in a worksheet. That worksheet contains a abnk statement
containing three columns first one as date second as payee information and
third naturally amount. The problem I had encountered is the payee
information has been split into numerous rows but in the same column. I want
to add up the payee information into one cell in a row where the first column
contains the date. I wrote the macro as follows but unsuccessful saying
object required at activecell.formula.cut or can I change the property from
formula to value. Does it works?
Worksheets("sheet1").Activate
Range("B26").Activate
Do
If ActiveCell.Formula = "" Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula.Cut
ActiveCell.Offset(-1, 0).Activate
ActiveCell.Formula.PasteSpecial (Operation =
xlPasteSpecialOperationAdd)
ActiveCell.Offset(1, 0).Activate
ActiveCell.Entire Row.Delete
ActiveCell.Offset(0, -1).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop Until ActiveCell.Row = ActiveSheet.Rows.Count

Please reply to this problem as soon as possible.

--
Chanikya


"Rowan Drummond" wrote:

See replies to similar query he

http://tinyurl.com/b2ajf

Hope this helps
Rowan

cndesu wrote:
Hi,
I want to write a macro which checks the end of worksheet in VBA. I wanted
to know are there any constants or function which identifies End of Active
Sheet like EOF as End of File.

Sub CleanUpData()
Dim lastRow as Long, i as Long
Dim sStr2 as String, sStr1 as String
Dim rng as Range
lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if isempty(cells(i,3).Value) then
sStr2 = Trim(cells(i,2))
sStr1 = Trim(cells(i-1,2))
cells(i,2).Clearcontents
cells(i-1,2).Value = Replace(sStr1 & " " & sStr2,chr(10),"")
end if
Next
On Error Resume Next
set rng = columns(3).Specialcells(xlBlanks)
On Error goto 0
if not rng is nothing then
rng.EntireRow.Delete
End if
End Sub

Test this on a copy of your data.

--
Regards,
Tom Ogilvy


"cndesu" wrote in message
...
Hi,
Thanks for the reply. Already I got the solution right away. But the
problm
for which I has asked is explained in detail as follows.
I wrote a macro in a worksheet. That worksheet contains a abnk statement
containing three columns first one as date second as payee information and
third naturally amount. The problem I had encountered is the payee
information has been split into numerous rows but in the same column. I
want
to add up the payee information into one cell in a row where the first
column
contains the date. I wrote the macro as follows but unsuccessful saying
object required at activecell.formula.cut or can I change the property
from
formula to value. Does it works?
Worksheets("sheet1").Activate
Range("B26").Activate
Do
If ActiveCell.Formula = "" Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula.Cut
ActiveCell.Offset(-1, 0).Activate
ActiveCell.Formula.PasteSpecial (Operation =
xlPasteSpecialOperationAdd)
ActiveCell.Offset(1, 0).Activate
ActiveCell.Entire Row.Delete
ActiveCell.Offset(0, -1).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop Until ActiveCell.Row = ActiveSheet.Rows.Count

Please reply to this problem as soon as possible.

--
Chanikya


"Rowan Drummond" wrote:

See replies to similar query he

http://tinyurl.com/b2ajf

Hope this helps
Rowan

cndesu wrote:
Hi,
I want to write a macro which checks the end of worksheet in VBA. I
wanted
to know are there any constants or function which identifies End of
Active
Sheet like EOF as End of File.