CHECK END OF WORKSHEET DATA

[Guest]

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.

 

[Rowan Drummond]

See replies to similar query here:

http://tinyurl.com/b2ajf

Hope this helps
Rowan

 

[Guest]

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.

 

[Tom Ogilvy]

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.