Using xlup not working

[Phillip Marley]

Hi all

I am trying to get the row number of the last cell with data in, in col A so that I can copy data from another sheet and put it under the existing data in the active sheet. the spreadsheet has been formatted down to row 5000 in alternate blue and white rows so when i try to find the last row with data in it comes up with with row 5000 which is the last formatted row. the actual last row with data is row 850.


my first code was

Dim J1 As Integer
J1 = Range("A65536").End(xlUp).Row

I tried the code below which returns myRng as a1:a850 (at least it finds the correct last row with data) but I need to store the cell number 850 as a parameter . how to I get that from the returned range.

Dim myRng As Range
Set myRng = Range("a1:a10000").SpecialCells(xlCellTypeConstants) ( I chose a10000 at random because the file will never get that big)

Thanks

Phill

 

[Fluff]

Is the data in a table, or just a normal range?

 

[Phillip Marley]

Hi Fluff

To answer your question It is a table, but after hours of searching the web I found a bit of code thad did what I wanted it is as follows (See below) this gave me the last row number of completed jobs on the Completed sheet of the spreadsheet.

The next section goes to the jobs sheet and filters by a column L to give the jobs that are complete

After this the next section copies and pastes the rows to the completed sheet in the row number found in the first section.

All this works now, but I am having difficulty with the last section where i am trying to delete the previously selected completed rows from the jobs sheet

I keep getting error 1004 Delete method of range class failed. Any chance that you might know what I am doing wrong. I am a novice VBA user and have probably got the last section of the macro completely wrong.


Sub Jordon()

'Move to completed sheet and find last cell then add 1 to create insertion row

Sheets("Completed").Select
Dim myRng As Range
Dim j1 As Integer
With Sheets("Completed").Range("a:a")
Set myRng = .SpecialCells(xlCellTypeConstants)
Set myRng = myRng.Areas(myRng.Areas.Count)
j1 = myRng.Cells(myRng.Cells.Count).Row
End With

Dim j2 As Integer
j2 = j1 + 1


'Filter by Completed field

Sheets("Jobs").Select
Range("Table1[[#Headers],[Status]]").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=12, Criteria1:= _
"COMPLETE"


'Select Visible cells

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:n" & LR).SpecialCells(xlCellTypeVisible).Select


'Copy Rows marked as complete to completed table

Selection.Copy
Sheets("Completed").Select

Range("A" & j2).Select
ActiveSheet.Paste


' Delete previous selected rows from jobs sheet

Sheets("Jobs").Select
Range("Table1[[#Headers],[Status]]").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=12, Criteria1:= _
"COMPLETE"
Dim LR2 As Long
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:n" & LR2).SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete


End Sub

 

[Fluff]

after hours of searching the web I found a bit of code thad did what I wanted

Glad you sorted it & thanks for the feedback.

I am having difficulty with the last section where i am trying to delete the previously selected completed rows

As this is a totally different question, it needs a new thread.
Thanks