Error in finding the last used cell

[zubin]

Hi All....
I am having Three Ranges in a worksheet, "B4:B14", "E4:E14", "F4:F14"

All the cells in the range "F4:F14" has formulae,

The Values of these ranges have to be copied in the Columns "B", "D" and "E" respectively
ie Values of Range "B4:B14" has to be copied in the last available cell (Row) in column "B"
Values of Range "E4:E14" has to be copied in the last available cell (Row) in column "D"
Values of Range "F4:F14" has to be copied in the last available cell (Row) in column "E"

The below code i am using does the work well, It copies the first two ranges "B4:B14", "E4:E14" correctly everytime without any error,
but the Range "F4:F14" is where the problem comes.....most of the time it copies the values, but with a gap of a few cell (Rows) in between,
please note the cells in this range has formulae.

Sheet1.Range("B4:B14").Copy
    Sheet1.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("E4:E14").Copy
    Sheet1.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    Selection.Value = Selection.Value
    
    Sheet1.Range("F4:F14").Copy
    Sheet1.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    Selection.Value = Selection.Value
        
      
    Application.CutCopyMode = False

I have tried various codes, but the error occurs

SCM Current-Dist-3ScreenCopy.xlsm
	A	B	C	D	E
184	11:47	KFSTM650B	BOX KFSTM650	1	1995
185	11:47	LPS650B	BOX LPS650	1
186	11:47	LPM650B	BOX LPM650	1
187	11:47	LPM330B	BOX LPM330	1
188	11:47	KFS650B	BOX KFS650	2
189	11:47	KFM650B	BOX KFM650	1
190	11:47	KFS330B	BOX KFS330	1
191
192
193
194
195					1600
196					1460
197					1824
198					3876
199					1938
200					2234.4
201
04-18-2021

Cell Formulas
Range	Formula
A184:A201	A184	=TIMESTAMP(B184)
C184:C201	C184	=IFERROR(VLOOKUP(B184,$M$1:$Q$6000,2,FALSE),"")

 

[mikerickson]

Perhaps

Sheet1.Range("E4:F14").Copy
Sheet1.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
Selection.Value = Selection.Value
    
' remove Sheet1.Range("F4:F14").Copy
' remove Sheet1.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
' remove Selection.Value = Selection.Value

 

[Rick Rothstein]

Sheet1.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues

Try changing the above line of code to this...

Sheet1.Range("E" & Sheet1.Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1).PasteSpecial xlValues

 

[zubin]

Try changing the above line of code to this...

[CODE=vba]Sheet1.Range("E" & Sheet1.Columns("E").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1).PasteSpecial xlValues

[/CODE]

Should it not be...

Sheet1.Range("E" & Sheet1.Columns("E").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1).PasteSpecial xlValues

 

[zubin]

Should it not be...

Sheet1.Range("E" & Sheet1.Columns("E").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1).PasteSpecial xlValues

So far the code is working good.....but it takes longer to execute...
Why was my previous code giving errors.

 

[Rick Rothstein]

Should it not be...

Sheet1.Range("E" & Sheet1.Columns("E").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1).PasteSpecial xlValues

Yes, it should have been "E", not "A" (bad editing on my part). Your code did not work because your method see formulas as filled cells even when display "". I don't see a reason why my method should be slower than yours.

 

[zubin]

Yes, it should have been "E", not "A" (bad editing on my part). Your code did not work because your method see formulas as filled cells even when display "". I don't see a reason why my method should be slower than yours.

Thanks a lot Rick.....

 

[zubin]

Thanks a lot Rick....

 

[zubin]

In continuation of above code.....every time a range is copied can all the cells in the range be given a color depending on the value of the cell "F15"
Say If "F15" < 1000 Color of the copied range "Green"
If "F15" > 1000 Color of the copied range "Blue"
If "F15" > 10000 Color of the copied range "Red"
And I have a List box which displays this range..... the same color of the range should show up in the listbox, Is this Possible
and is this within the scope of this Tread

Or

Should i start a new tread