invalid or unqualified reference

[satish.nl]

i need to copy 1st row(from A to H cols) after applying filter my code looks like below


but im getting compile error when im seleting the filtered row


i need to copy the contents in same worksheet but in different column.


Please help me in solving the issue




ActiveSheet.Range(batStartRow & ":" & r).AutoFilter Field:=1, Criteria1:="Ad"
ActiveSheet.Range(batStartRow & ":" & r).AutoFilter Field:=3, Criteria1:=intMaxVal
ActiveSheet.Range(batStartRow & ":" & r).AutoFilter Field:=4, Criteria1:=intMaxVal1
ActiveSheet.Range(batStartRow & ":" & r).AutoFilter Field:=1




Dim LR As Long
ERROR LR = Range(batStartRow & ":" & r & Rows.Count).End(.xlUp).Row
Range("A:H" & LR).SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Range("L21").Select
ActiveSheet.Paste



Thanks in advance

[PAB]

Hi satish.nl,

Give this a go...


Dim LR As Long

LR = Range(batStartRow & ":" & r & Rows.Count).End(.xlUp).Row
Range("A:H" & LR).SpecialCells(xlCellTypeVisible).Copy Destination("L21").Paste

Also, what is the first cell the copy starts?

I hope this helps!

[p45cal]

this line is going to fail for 2 reasons:
LR = Range(batStartRow & ":" & r & Rows.Count).End(.xlUp).Row
1.there should not be a dot (red above) before xlUp (that's the invalid/unqualified reference).
2.let's just say you have set r to 5, and batStartRow to 10 somewhere, then this bit:
Range(batStartRow & ":" & r & Rows.Count)
translates to:

Range(10:51048576)
where I've colour coded the parts. This is an impossible range (more than there are rows on the sheet).
So what were you hoping to achieve with that line (in English, not code)?

Currently, with the values above and some assumptions on my part, it finds the row of the last non-empty cell in column A above row 10.

[mancubus]

use code tags when posting your code here. (see my signature)

assuming topleft cell of your table is A1 try this.



Sub vbax_56028_firstvisiblerow_autofilterrange()

Dim FVRow As Long, NumCols As Long

With ActiveSheet
.Cells(1).AutoFilter 3, intMaxVal
.Cells(1).AutoFilter 4, intMaxVal1
With .AutoFilter.Range
FVRow = .Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
NumCols = .Columns.Count
End With
.Rows(FVRow).Resize(, NumCols).Copy .Range("L21")
.AutoFilterMode = False 'clear filter
End With
End Sub


since the fourth line in your code clears the filter applied by first line, these lines are redundant.

[Aflatoon]

Cross-posted FYI: http://www.mrexcel.com/forum/excel-questions/940774-compile-error-invalid-unqualified-reference.html

[satish.nl]

Thanks p45cal for the the suggestion sorry for not using code tags


actually my topleft cell is not A1

i will be searching for a pattern in Col A and storing the cell address in "batStartRow" ,based on this address i need to apply filter ,
i need to copy the 1st visible row of the filtered table

[mancubus]

when you post a question to multiple forums, provide the links to all threads in other forums. and do this in all the threads you have opened.


"assuming" means "adopt the code to your case/table/workbook."

if the topleft cell is not A1 then replace .Cells(1) with that cell in your table.
Example: .Range("B8")

posting the workbook and related code will make helpers help you faster.