variable use in range

[crapit]

Regarding range() usage, I had a non-fix range value
where by "lastrow" has been assigned to a variable value
range("P1:S710")

But how do I make it to function as below??
Sheets("Status").Range(Cells(1, 16), Cells(lastrow, 19)).Copy
Sheets(shtName).Range("b1")

 

[crapit]

No one can help? Basically it is to COPY from 1 worksheet to another
worksheet but I keep on getting error msg as follow:

Run-time error '1004':

Application-defined or object-defined error
~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

Dim shtName As String
shtName = filtered

 

[Frank Kabel]

Hi
you may post the complete code as I'm not sure what you assign to
lastrow and what your expected result is

 

[crapit]

Sub test_copy()
Dim lastrow As Long
Dim shtName As String
shtName = "Filtered"


Sheets.Add.Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
copy:
lastrow = Sheets("Status").Cells(Rows.Count, 19).End(xlUp).Row

Worksheets("Status").Range(Cells(1, 16), Cells(lastrow, 19)).copy
Worksheets(shtName).Range("b1")

End Sub

 

[crapit]

How to the 2nd visible row if autofilter is turn on?

 

[Dave Peterson]

I think I'd qualify those cells().

Worksheets("Status").Range(Cells(1, 16), Cells(lastrow, 19)).copy

One way:

with worksheets("status")
.range(.cells(1,16),.cells(lastrow,19)).copy _
destination:=Worksheets(shtName).Range("b1")
end with

 

[Dave Peterson]

What does second visible row mean?

Is the first visible row the headers and the second the first data row you can
see?

Or is it the second data row you can see?

If it's the first data row you can see:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count < 2 Then
MsgBox "not enough visible cells"
Exit Sub
Else
Set myRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
myRng.Cells(1).Select
End If
End With

End Sub



If it's the second data row you can see:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count < 3 Then
MsgBox "not enough visible cells"
Exit Sub
Else
Set myRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

If myRng.Areas(1).Rows.Count > 1 Then
myRng.Offset(1, 0).Cells(1).Select
Else
myRng.Areas(2).Cells(1).Select
End If
End If
End With

End Sub

I'm not sure what you're doing, so I just selected that cell.

 

[crapit]

Thanks for this reply, but why is Destination:= needed?

 

[crapit]

I wanted to check what is the row number on the row if autofilter is set!

E.g 2nd row will be 632. But how do I get that?
F G H I J K
1
632
635
638
641
644
647

 

[Dave Peterson]

It's not. I just like to use it. I think it makes it easier to read later.

 

[Dave Peterson]

That would mean you want to see the first Data row. So use the first macro.

 

[crapit]

wat happen if certain rows are hidden?

That would mean you want to see the first Data row. So use the first
macro.

 

[Dave Peterson]

What happened when you tried it?

Anything bad?

wat happen if certain rows are hidden?

 

[crapit]

Error message "No cell were found"
The following were highlighted
Set myRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

 

[Dave Peterson]

You'll want to post the code you used.

I don't think either of the procedures I suggested will generate that error.

Error message "No cell were found"
The following were highlighted
Set myRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

 

[crapit]

I'm using your code! Perhaps the reason why I need to find out the location
of the visible 2nd row number is that I need to copy a specific number of
rows to another worksheet. But the rows that are to be copied are of 3
merged cells each.

 

[Dave Peterson]

Share the addresses of the visible cells and the addresses of the merged cells.

I'll try to set up a test workbook mimicking your layout.

I'm using your code! Perhaps the reason why I need to find out the location
of the visible 2nd row number is that I need to copy a specific number of
rows to another worksheet. But the rows that are to be copied are of 3
merged cells each.

 

[crapit]

F G H
1
631 -> hidden
632 -> visible
633 -> hidden
634 -> hidden
635 -> visible
636 -> hidden
637 -> hidden
638 -> visible
639 -> hidden

Even if i manually select F632,F635,F638 and so on till the last row of data
follow by copy command
"cannot change part of a merge cell" error message

 

[Dave Peterson]

Which cells are merged?

Is it row (Fxx:Hxx) or column (Fxx:Fyy) or both?

Merged cells take a little more work. I try not to use them.

F G H
1
631 -> hidden
632 -> visible
633 -> hidden
634 -> hidden
635 -> visible
636 -> hidden
637 -> hidden
638 -> visible
639 -> hidden

Even if i manually select F632,F635,F638 and so on till the last row of data
follow by copy command
"cannot change part of a merge cell" error message

 

[crapit]

Only the rows are merged. E.g 631 to 633, 634 to 636, 637 to 639
F G H