CHECK END OF WORKSHEET DATA
 

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

CHECK END OF WORKSHEET DATA
 

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.

CHECK END OF WORKSHEET DATA
 

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.

CHECK END OF WORKSHEET DATA
 

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.