Hi, Need small help...VBA to start copying from x defined area...

[pedie]

Hi, Need small help...VBA to start copying from x defined area...
from filtered result I want to copy from x row to last row from col A to K as shown below....this text way wont work...
For now i have my myindex as 4 which mean that i want to start copying from col. A lastrow - index [visible cells] to K last visible row...

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub Test1()[/FONT]
[FONT=Courier New]Dim datalr As Long[/FONT]
[FONT=Courier New]Dim myindex As Integer[/FONT]
[FONT=Courier New]myindex = 4[/FONT]
[FONT=Courier New]datalr = Sheet1.Range("A" & Rows.count).End(xlUp).Row[/FONT]
[FONT=Courier New]MsgBox datalr[/FONT]
[FONT=Courier New]   With Sheet1[/FONT]
[FONT=Courier New]   .AutoFilterMode = False[/FONT]
[FONT=Courier New]   .Range("$A$1:$K$" & datalr).AutoFilter Field:=11, Criteria1:="2"[/FONT]
[FONT=Courier New]   .Range("$A$1:$K$" & datalr).AutoFilter Field:=1, Criteria1:="EMP 3"[/FONT]
[FONT=Courier New]   datalr = .Range("A" & Rows.count).End(xlUp).Row[/FONT]
[FONT=Courier New]   MsgBox datalr[/FONT]
[FONT=Courier New]   .Range("A" & datalr - myindex & ":K" & datalr).SpecialCells(xlCellTypeVisible).Copy[/FONT]
[FONT=Courier New]   End With[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[Norie]

Pedie

What do you actually want to copy?

The last 4 rows of visible data?

 

[pedie]

Hi and yes Norie....
if my index is 1 then only last visible cell,2 then last 2 visible cell..and so on...

Thanks for helping in advance.

 

[meldoc]

Hi Pedie

Is It?

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Test1()<br><SPAN style="color:#00007F">Dim</SPAN> datalr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myindex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>myindex = 4<br>datalr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row<br>MsgBox datalr<br>   <SPAN style="color:#00007F">With</SPAN> Sheet1<br>   .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>   .Range("$A$1:$K$" & datalr).AutoFilter Field:=11, Criteria1:="2"<br>   .Range("$A$1:$K$" & datalr).AutoFilter Field:=1, Criteria1:="EMP 3"<br>   datalr = .Range("A" & Rows.Count).End(xlUp).Row<br>   MsgBox datalr<br>   .Range("A" & myindex & ":K" & datalr).SpecialCells(xlCellTypeVisible).Copy<br>   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Norie]

Pedie

Visible cells will only return the visible cells in the specified range.

So even if you are using a range with 4 rows, if only 2 of those are visible only 2 will be copied when you use that your code.

4 rows will only be copied when all 4 rows are visible.

 

[pedie]

Hi Brian!!!

Actualy i want to copy last x number of visible cells from filtered result...
if i do it in a longer way....then it looks like this...however i do not want to copy across visible cells and find last x cells instead directly copy last x rows of cells directly from filtered result...

if my index is 4 then i want to copy last 4 visible cells...
Sorry if the explaination confused you...

Thanks again for helping
Pedie

[/FONT]
[FONT=Courier New]Sub PartTest1()
Dim datalr, datalri As Long
Dim myindex As Integer
myindex = Sheet7.Range("P1").Value
datalr = Sheet1.Range("A" & Rows.count).End(xlUp).Row[/FONT]
[FONT=Courier New]    With Sheet1
    .AutoFilterMode = False
    .Range("$A$1:$K$" & datalr).AutoFilter Field:=11, Criteria1:="2"
    .Range("$A$1:$K$" & datalr).AutoFilter Field:=1, Criteria1:="EMP 3"
    .Range("A1:K" & datalr).SpecialCells(xlCellTypeVisible).Copy
    End With
    Sheet7.Select
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    datalri = Sheet7.Range("D" & Rows.count).End(xlUp).Row
    Range("D" & (datalri - myindex) + 1 & ":N" & datalri).Copy
End Sub

Hi Pedie

Is It?

Option Explicit
Sub Test1()
Dim datalr As Long
Dim myindex As Integer
myindex = 4
datalr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox datalr
With Sheet1
.AutoFilterMode = False
.Range("$A$1:$K$" & datalr).AutoFilter Field:=11, Criteria1:="2"
.Range("$A$1:$K$" & datalr).AutoFilter Field:=1, Criteria1:="EMP 3"
datalr = .Range("A" & Rows.Count).End(xlUp).Row
MsgBox datalr
.Range("A" & myindex & ":K" & datalr).SpecialCells(xlCellTypeVisible).Copy
End With
End Sub

 

[pedie]

Pedie

Visible cells will only return the visible cells in the specified range.

So even if you are using a range with 4 rows, if only 2 of those are visible only 2 will be copied when you use that your code.

4 rows will only be copied when all 4 rows are visible.

Okay and thanks again...
So is there a way to achieve what 'm trying to do...?

 

[meldoc]

Ok I think That you have answered your own question, Try.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Test1()<br><SPAN style="color:#00007F">Dim</SPAN> datalr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myindex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>myindex = 4<br>datalr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row<br>MsgBox datalr<br>   <SPAN style="color:#00007F">With</SPAN> Sheet1<br>   .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>   .Range("$A$1:$K$" & datalr).AutoFilter Field:=11, Criteria1:="2"<br>   .Range("$A$1:$K$" & datalr).AutoFilter Field:=1, Criteria1:="EMP 3"<br>   datalr = .Range("A" & Rows.Count).End(xlUp).Row<br>   MsgBox datalr<br>   .Range("A" & (datalr - myindex) + 1 & ":K" & datalr).SpecialCells(xlCellTypeVisible).Copy<br>   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[pedie]

Brian, not really...close but nope...
Here is one of the link you help me with in the past:

I took the idea from there and work kinda great till now...hope it does not give me problem...

if anyone have better/faster way of doing this pleaese lemmi know.

Brain, Norie....thanks again guys for helping me out!

[/FONT]
[FONT=Courier New]Sub Test_2()
Dim lr As Long
Dim myi, VisCount As Integer[/FONT]
[FONT=Courier New]myi = 3
lr = Sheet1.Range("A" & Rows.count).End(xlUp).Row
VisCount = 0
For i = lr To 2 Step -1
  If Rows(i).EntireRow.Hidden = False Then
   VisCount = VisCount + 1
    If VisCount >= myi Then
      Cells(i, "A").Select
      Range(Cells(i, "A"), Cells(lr, "K")).Copy
      Exit For
    End If
  End If
Next i
End Sub

 

[Norie]

This worked with a small sample set of data.

Option Explicit

Sub Test1()
Dim datalr As Long
Dim myindex As Integer
Dim rng As Range
Dim I As Long
Dim J As Long
Dim rngResult As Range
Dim X As Long

    myindex = 4

    datalr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    With Sheet1

        .AutoFilterMode = False

        .Range("$A$1:$B$" & datalr).AutoFilter Field:=2, Criteria1:="1"
 
        datalr = .Range("A" & Rows.Count).End(xlUp).Row
 
        Set rng = .Range("A" & 2 & ":B" & datalr).SpecialCells(xlCellTypeVisible)

    End With
 
    I = rng.Areas.Count
 
    Do
        J = rng.Areas(I).Rows.Count

        Do
 
            If rngResult Is Nothing Then
                Set rngResult = rng.Areas(I).Rows(J)
                X = 1
            Else
                X = X + 1
                Set rngResult = Union(rngResult, rng.Areas(I).Rows(J))
            End If
 
            J = J - 1
 
        Loop Until J = 0 Or X = myindex
 
        I = I - 1
 
    Loop Until I = 0 Or X = myindex
 
End Sub