Fetching Lines in reverse order 1

[ddpoo06]

Hi,
I have created a macro script in MS excel for a button which will fetch some data from a different excel sheet over some conditions if matches. But the problem is that the order of the lines which is being extracted is in reverse. For example:
1.This should be line 5
2.This should be line 4
3.This should be line 3
4.This should be line 2
5.This should be line 1
and so on...

Here's the code that i have developed. Frankly speaking i'm not a programmer, i have just created it by the help of google help. Can anyone help me to make it in correct order. Thanks a ton in advance.




Sub CreateExtDep()

Sheets.Add.Name = "ExtDep"
Dim i As Long
Sheets("T&C Report").Select
For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If UCase(ActiveSheet.Cells(i, 19).Value) = "BEC" Then
ActiveSheet.Rows(i).Copy Sheets("ExtDep").Cells(TargetRow(Sheets("ExtDep"), 1), 1)
' ActiveSheet.Rows(i).Delete
End If
Next i
Sheets("T&C Report").Select
Rows("1:4").Select
Selection.Copy
Sheets("ExtDep").Select
ActiveWindow.SmallScroll Down:=-6
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial xlPasteColumnWidths
Columns("A:X").EntireColumn.AutoFit
Columns("F").EntireColumn.Delete
Rows("5:5").Select
ActiveWindow.FreezePanes = True
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Sheets("Summary").Select
End Sub

Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
'change targetrow to 2 to move the rows starting on the second row
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function




Please help me out...

 

[SkipVought]

hi,

Lets clarify:

You want to copy ENTIRE ROWS from T&C Report to ExtDep in reverse order so that, assuming you have 5 rows, row 5 from T&C Report becomes row 1 in ExtDep and so forth?

If that's the case, then you must not have any heading rows, correct? That would be an odd workbook!

But if you just want the same order, then change this statement

For i = 2 to TargetRow(ActiveSheet, 1)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ddpoo06]

Hi SkipVought,

Thanks a lot Sir,
yes it worked...

 

[SkipVought]

For the benefit of other members who browse for ideas, please explain what it was that "worked."

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ddpoo06]

Hello,

The program which i wrote was to extract some data from another sheet after matching some conditions. But always i got the data in descending order. Means every time i have to make transpose of the fetched data to make it in correct order. The data in the last line always was on the top of the queue. The change suggested by SkipVought by reversing the 'for loop' statement (i.e,
from 'For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1' to 'For i = 2 to TargetRow(ActiveSheet, 1)' make the data fetched in ascending order.
Example:

1. This should be line 1 (Building 255)
2. This should be line 2 (Building 254)
3. This should be line 3 (Building 253)
4. This should be line 4 (Building 251)
5. This should be line 5 (Building 250)
.
.
.
.
15. This should be line 5 (Building 240)

(Note: There are n number of lines. Refer the snap of the T&C Report Sheet)

Thanks for the help.
Hope i'm clear about the issue.
Here's the rectified code:


Sub CreateExtDep()

Sheets.Add.Name = "ExtDep"
Dim i As Long
Sheets("T&C Report").Select
For i = 2 to TargetRow(ActiveSheet, 1)
If UCase(ActiveSheet.Cells(i, 19).Value) = "BEC" Then
ActiveSheet.Rows(i).Copy Sheets("ExtDep").Cells(TargetRow(Sheets("ExtDep"), 1), 1)
' ActiveSheet.Rows(i).Delete
End If
Next i
Sheets("T&C Report").Select
Rows("1:4").Select
Selection.Copy
Sheets("ExtDep").Select
ActiveWindow.SmallScroll Down:=-6
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial xlPasteColumnWidths
Columns("A:X").EntireColumn.AutoFit
Columns("F").EntireColumn.Delete
Rows("5:5").Select
ActiveWindow.FreezePanes = True
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Sheets("Summary").Select
End Sub

Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
'change targetrow to 2 to move the rows starting on the second row
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function


Thanks,
ddpoo06

 

[Andrzejek]

Welcome to TT, ddpoo06
It is customary to show appreciation for help received. To do so, please click on “[blue]Great Post[/blue]” link on Skip’s post to give him a star.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.