Selecting range based on another cell last row (VBA)

[feni1388]

Hello....
Please help.....
I don't know what's wrong with this code.

Range("D6:G6").Select
Range("D6:G" & Range("F" & Rows.Count).End(xlUp).Row).Select

At this part, it ended up selecting A6:G39. Even though column F last row is F8.
There's no merge cells in all columns D to G.

Sub CopyData()

Sheets("Search").Select
Range("D6:G6").Select
Range("D6:G" & Range("F" & Rows.Count).End(xlUp).Row).Select


Sheets("NextPO").Select
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
Application.ScreenUpdating = True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

Sheets("Search").Select
    
End Sub

先日付確認用-test.xlsm
	A	B	C	D	E	F	G	H
3		Delivery address				Item	Qty	Check
4
5	Search by Phone No　－→					DAB01165
6	Company A			DAB01165	1	GL43	10
7					2	GL44	10
8					3	JT05	5
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
検索

Cell Formulas
Range	Formula
F5	F5	=IF(AND(C5<>"",D6<>""),"検索条件を修正してください",IF(C5<>"",VLOOKUP(SUBSTITUTE(TRIM(C5),"-",""),得意先及び直送先!U:V,2,FALSE),D6))
A6	A6	=IF(D6="","",VLOOKUP(D6,得意先及び直送先!A:K,2,FALSE)&VLOOKUP(D6,得意先及び直送先!A:F,3,FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5	Cell Value	contains "検索"	text	NO
A6	Cell Value	contains "様"	text	NO

 

[SunnyAlv]

try

Sheets("Search").Select
Range("D6").Select
Range("D6:F" & Cells(Rows.Count, "F").End(xlUp).Row).Select

..............................

 

[feni1388]

Still the same.

 

[Cubist]

In the immediate window, paste this and hit enter. What does it return?

Debug.Print Sheets("Search").Range("F" & Rows.Count).End(xlUp).Row

 

[feni1388]

Nothing comes up. But when I hover it on the Rows.Count part, it shows 1048576

I tried running it after selecting D6 cell.
but it stopped at Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select part

 

[jolivanes]

Try So
Check and change references where required

Sub How_About_So()
Dim shS As Worksheet, shNP As Worksheet
Dim lr As Long
Set shs = Worksheets("Search")
Set shNP = Worksheets("NextPO")
lr = shS.Columns(6).Find("*", ,xlValues , , xlByRows, xlPrevious).Row
shNP.Cells(shNP.Rows.Count, 3).End(xlUp).Offset(1).Resize(lr - 5, 4).Value = shS.Cells(6, 4).Resize(lr - 5, 4).Value
End Sub

You have an overabundance of selecting which is normally frowned upon because of slowing down code.

 

[jindon22]

Do you mean?

    Dim x As Long
    With Sheets("search")
        With Intersect(.Columns("d:g"), .UsedRange)
            x = .Parent.Evaluate("max(if(" & .Address & "<>"""",row(" & .Address & ")))")
        End With
        If x Then
            .Range("d6:g" & x).Select
        Else
            MsgBox "No data to select"
        End If
    End With

 

[feni1388]

Try So
Check and change references where required

Sub How_About_So()
Dim shS As Worksheet, shNP As Worksheet
Dim lr As Long
Set shs = Worksheets("Search")
Set shNP = Worksheets("NextPO")
lr = shS.Columns(6).Find("*", ,xlValues , , xlByRows, xlPrevious).Row
shNP.Cells(shNP.Rows.Count, 3).End(xlUp).Offset(1).Resize(lr - 5, 4).Value = shS.Cells(6, 4).Resize(lr - 5, 4).Value
End Sub

You have an overabundance of selecting which is normally frowned upon because of slowing down code.

Your code is really close to what I expect to do.
But it ended up copying the total qty in column G (cell G33) and when I change the customer code, items and qty and run the macro again, it will paste it over the previous record (which has the last row on column F or G). I want the information to be pasted below the last used cell of Row "G", so the list can be continued.

 

[feni1388]

Do you mean?

    Dim x As Long
    With Sheets("search")
        With Intersect(.Columns("d:g"), .UsedRange)
            x = .Parent.Evaluate("max(if(" & .Address & "<>"""",row(" & .Address & ")))")
        End With
        If x Then
            .Range("d6:g" & x).Select
        Else
            MsgBox "No data to select"
        End If
    End With

I tried your code but it selects other columns too.

 

[jindon22]

This is to Select D6 to Gx, where x is the last row that have the value within col.D:F.
So, adjust for yourself, if needed.

    Dim x As Long
    With Sheets("search")
        With Intersect(.Columns("d:f"), .UsedRange)
            x = .Parent.Evaluate("max(if(" & .Address & "<>"""",row(" & .Address & ")))")
        End With
        If x Then
            .Range("d6:g" & x).Select
        Else
            MsgBox "No data to select"
        End If
    End With