Values between sheets

[cerins4ever]

Hi guys, i have one problem with data transfer between sheets, hope someone professional will give me answer.
I have MsgBox code which is not working, where is problem?
Is it problem with that in the code are line .End(xlUp).SpecialCells(xlCellTypeVisible) or what?

Private Sub CommandButton1_Click()

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Dim i1 As Integer
i1 = 1
Do While i1 < Sheet2.Range("A4:A" & LR).SpecialCells(xlCellTypeConstants).Count + 1


MsgBox Sheet1.Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(Sheet2.Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(i1, 1).Value - 3, 2).Value


    i1 = i1 + 1
Loop
End Sub

Thanks a lot!

 

[Andrew Poulsom]

That's because End(xlUp) bypasses hidden rows.

 

[cerins4ever]

That's because End(xlUp) bypasses hidden rows.

Yes it's true
I'm using following formula to find last row even it's hided

 Dim ws As Worksheet
Dim temp As Worksheet
    Dim lastrow As Long
    Set ws = Sheets("Nepieciešams")
    ws.Copy Before:=Sheets(1)
    Set temp = ActiveSheet
    If temp.AutoFilterMode Then temp.AutoFilterMode = False
    temp.Columns("A:A").EntireRow.Hidden = False
    lastrow = temp.Range("A" & temp.Rows.Count).End(xlUp).Offset(1, 0).Row
    Application.DisplayAlerts = False
    temp.Delete
    Application.DisplayAlerts = True

Is't shortest way? if it's not Maybe you can recommend shorter way

 

[Andrew Poulsom]

It's tricky with AutoFilter. In your particular case you can use:

lastrow = Sheets("Nepieciešams").Range("A1").CurrentRegion.Rows.Count

 

[cerins4ever]

It's tricky with AutoFilter. In your particular case you can use:

lastrow = Sheets("Nepieciešams").Range("A1").CurrentRegion.Rows.Count

yup it's tricky and on web there are less answers on this question, looks like code

[COLOR=#333333]Sheets("Nepieciešams").Range("A1").CurrentRegion.Rows.Count[/COLOR]

will no be suitable for me, because also these ID codes i will hide.

Thanks a lot any way for help.
you are professional!
My code now are 4 x times shorter and lighter also!

 

[Andrew Poulsom]

The code I posted will still work if you hide/AutoFilter.

 

[cerins4ever]

The code I posted will still work if you hide/AutoFilter.

unbelievable
Thanks!