Folks:
I finally got my find last row formula to work but I can't seem to get it to work inside my macro with other variables.
Note: My previous copy data was contiguous 1 region but now I need to copy non-contiguous copy data with the parameters in this code (B1:M & lastrow)
Also, I want to use an array for worksheets so that its easier to add and remove cost centers.
Finally, if I can get this licked, I'll need a separate macro with a formula for first row, since I have another set of data that resides below a table I don't need.
Thank you - Rowland
This worked:
START CODE:
Sub testing123()
Dim LastRow As Long
With Worksheets("4050CC30001")
LastRow = Worksheets("4050CC30001").Cells(Rows.Count, "B").End(xlUp).Row
.Range(Cells(1, 2), Cells(LastRow, 13)).Select
End With
End Sub
END CODE
Now how do I get that to work within this:
START CODE:
'Completed Code ? Sample CC
Option Explicit
Sub Populate_line_item_Workbook_Browser_Method()
Dim MasterWB As Workbook
Dim SourceWB As Workbook
Dim rngSrc As Range
Dim rngDst As Range
Dim ws As Worksheet
Dim varFileName As Variant
Dim I As Long
Dim myArr As Variant
Dim LastRow As Long
Set MasterWB = Workbooks("Line items-Combined16.xlsm")
''''''''''Clear MasterWB'''''''''''''''''''''''''''''''''''''''''''''''''''''
MasterWB.Sheets("Master-Incoming").Activate
Rows("3:3").Select
Range("E3").Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("a1").Activate
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
?"4050CC30001", "301AA1234", "50BB9999" and "65961LL3201"
myArr = Array("4050CC30001", "301AA1234", "50BB9999", "65961LL3201")
'For I = LBound(myArr) To UBound(myArr) ? Don?t know how to make array work
varFileName = Application.GetOpenFilename(, , "Please select source workbook:")
If TypeName(varFileName) = "String" Then
Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0)
For Each ws In SourceWB.Worksheets
If ws.Name Like "4050CC30001" And ws.Visible <> xlSheetHidden Or _
ws.Name Like "301AA1234" And ws.Visible <> xlSheetHidden Or _
ws.Name Like "50BB9999" And ws.Visible <> xlSheetHidden Or _
ws.Name Like "65961LL3201" And ws.Visible <> xlSheetHidden Then
'Expand Column groups, Collapse Row groups ? need to hide lower table
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
'copy
?This works for contiguous data region, but my new data is not contiguous:
?Set rngSrc = ws.Range("A3").CurrentRegion.Offset(1, 0)
LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
?I can?t get this to work:
Set rngSrc = ws.Range(Cells(1, 2), Cells(LastRow, 13))
'paste
Set rngDst = MasterWB.Sheets("Master-Incoming").Range("A" & Rows.Count).End(xlUp).Offset(1)
rngSrc.Copy
rngDst.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next ws
SourceWB.Close False
?On contiguous data, got error message when it ran out of new sheets so I _
added MsgBoxes:
MsgBox "Copied all data from source workbook"
Else
MsgBox "No file selected"
End If
Application.Goto MasterWB.Worksheets("Master-Incoming").Range("A1"), True
End Sub
END CODE
I have a worksheet in which I have deleted rows at the end of the sheet in
some VBA code. In VBA I now want to find the last last row that contains
data. Some columns have data and some are blank so I don't know which column
will consistently have data.
What is the correct code I need to use to return the last row number that
contians data?
TIA.
--
Ken Hudson
On Thursday, December 13, 2007 12:14 PM James_Thomlinso wrote:
I use this function a lot in my projects. It returns the last cell. You can
easily get the row from that something like this...
sub test
msgbox lascell(sheets("Sheet1")).row
end sub
Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer
If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function
--
HTH...
Jim Thomlinson
"Ken Hudson" wrote:
On Friday, December 14, 2007 3:33 AM Carim wrote:
Hi,
Have a try with :
Sub GetRealLastRow()
Dim RealLastRow As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).row
Cells(RealLastRow, 1).Select
End Sub
HTH
