PDA

View Full Version : Help with ranges/skip hidden rows in VBA Code



Cartolo
06-12-2017, 04:56 PM
Hi All. I am using this code to copy data from 3 columns from workbook 'x' to workbook 'y'.

I need to copy and paste the workbook rows from one another but I get the error "1004": Method 'Range' of object'_Global' failed right around
Range("Q3:R3" & i +1).

Another issue when I try to skip the hidden rows from book 'x' so they do not appear in book 'y'. I do not see the rows removed. Here is the code below. Thank you.





Sub GetDataDemo()
Dim FilePath$
Dim i As Long
Const FileName$ = "EMS.xlsx"
Const SheetName$ = "PO"
FilePath = "C:\Users\DD\Desktop\"


DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1, 0).Select
End If

End If
For i = 3 To 500
Range("Q3:R3" & i +1) = GetData(FilePath, FileName, SheetName, Range("Y2:A2" & i))
Next i
ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Rng)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Rng.Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

SamT
06-12-2017, 09:28 PM
This is just wrong
Range("Q3:R3" & i +1) It results in: Range("Q3:R34") all the way up to Range("Q3:R3501")

I think you want

Range(Cells(i, "Q"), Cells(i + 1, "R"))
which is the same as

Range("Q" & i & ":R" & i+1)But even that is a two row by two column range

Rethink the other mentioned range also (Y:A)

xld
06-13-2017, 12:06 AM
This might get you closer


Sub GetDataDemo()
Const FileName As String = "EMS.xlsx"
Const SheetName As String = "PO"
FilePath = "C:\Users\DD\Desktop\"
Dim wb As Workbook
Dim this As Worksheet
Dim i As Long, ii As Long

Application.ScreenUpdating = False

If IsEmpty(Dir(FilePath & FileName)) Then

MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Else

Set this = ActiveSheet

Set wb = Workbooks.Open(FilePath & FileName)

With wb.Worksheets(SheetName).Range("A2:Y2")

ii = 3
For i = 3 To 500

If Not .Rows(i).Hidden Then

.Cells(i).Copy
this.Range("R3").Cells(ii).Paste
ii = ii + 1
End If
Next i
End With
End If

ActiveWindow.ScreenUpdating = True
End Sub

Cartolo
06-13-2017, 11:12 AM
It works fine until the ranges. I get this error: Run-time error '1004': Method 'Range' of object '_Global' failed.

Around this section.
With wb.Worksheets(SheetName).Range("A2" & i)

Cartolo
06-13-2017, 11:22 AM
It works fine until the ranges. I get this error: Run-time error '1004': Method 'Range' of object '_Global' failed.

Around this section.
With wb.Worksheets(SheetName).Range("A2" & i)

xld
06-13-2017, 03:39 PM
I can't see that in my code.

Cartolo
06-13-2017, 05:39 PM
I meant the
With wb.Worksheets(SheetName).Range("A2:Y2") creates the error.