End of column text vba

[ash59]

I have an convoluted macro that takes 3 columns of info from one sheet, rearranges it on the same sheet, cuts it and then dumps into another sheet. I am not that confident with VBA so the second sheet has a straight copy from the first, including 3 columns down to row 671. The problem that I have is that the written data could be anything from 5 rows to 671. If it is less than 671 ctrl end goes to row 671 even though there is no written data in it. How do I set the spreadsheet, via vba, so that ctrl end goes to the end of the written data?

 

[ash59]

on the 3rd option, I am getting Method of "Range" of object "_global" failed notification

Range("C1").Select
    Dim rng As Range
    Set rng = Range("C1:C" & lastRow)
    ActiveWorkbook.Save

and the same with this

Sheets("Sheet1").Select
    Range("C1").Select
    Range("C" & lastRow).Select
    ActiveWorkbook.Save

 

[Joe4]

on the 3rd option, I am getting Method of "Range" of object "_global" failed notification

Range("C1").Select
    Dim rng As Range
    Set rng = Range("C1:C" & lastRow)
    ActiveWorkbook.Save

Where is your "lastRow" calculation?
Since you are calling "lastRow", you still need that calculation.

I was just showing you code snippets of what you can do with "lastRow" after you calculate it. So you still need the calculation in those cases, i.e.

Dim lastRow As Long
Dim rng As Range
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set rng = Range("C1:C" & lastRow)

 

[ash59]

Where is your "lastRow" calculation?
Since you are calling "lastRow", you still need that calculation.

I was just showing you code snippets of what you can do with "lastRow" after you calculate it. So you still need the calculation in those cases, i.e.

Dim lastRow As Long
Dim rng As Range
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set rng = Range("C1:C" & lastRow)[/CODE
[/QUOTE]

 

[ash59]

If (big if) I am reading this right, it should be set as

lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set rng = Range("C1:C" & lastRow)

which should give me a highlighted row down to the last piece of data? It does not seem to work

I am getting to the point of just doing it the old fashion way, scroll down and ctrl up as I can't seem to get it working

 

[Joe4]

lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set rng = Range("C1:C" & lastRow)

which should give me a highlighted row down to the last piece of data? It does not seem to work

Once again, these are just setting variables, it is not doing anything with them. It is up to you to determine what you want to do with it once you have it.

If you wanted to select the range, you could then use:

rng.Select

If you wanted to cut or copy the range, you would use:

rng.Cut

or

rng.Copy

If you wanted to change the color (or other formatting) of the range, you could do something like:

rng.Interior.Color = 65535

The methods I have showed you in the previous posts are just ways to build a dynamic range based on the data size.
Once you have that, you can do whatever you want with it.

 

[ash59]

Ok, finally got my head around what you are saying and it does what you say it does but I refer back to my original question. The selection does not only select cells with data in them. It also selects all the rows below it that show as blanks. However, I think I know what the problem is. The values are being created by a formula that contains =if ("","") and the columns are being copied on from another spreadsheet and pasted using values. Would the paste carry over the "" and set the cell as formatted?

 

[Joe4]

The values are being created by a formula that contains =if ("","") and the columns are being copied on from another spreadsheet and pasted using values. Would the paste carry over the "" and set the cell as formatted?

Yes, I believe that is true.

If I recall correctly, I believe the "Find" method described in the link below may work on data created in such a way:

3 Best Ways to Find Last non-blank Row and Column Using VBA

Learn how to get last used row and column in excel vba. Here I have illustrated 3 best way to get last row with data. 1st method is best and I prefer it to get last non-empty cell.

www.exceltip.com

Give it a test, and see if that method works for you.