Find the Last Row and Column with contents

[Apple1]

I want to highlight some cells in my worksheet. My worksheet may have >1 table, with empty rows or title between the tables.

Example

1 10 100
15 59 200

title1
1 10 100
15 59 200

I tried the below code, but only the cells in the 1st column get highlighted.
Thank you.


Sub SmallSample()
Dim SourceSht As Worksheet

Dim rngCopy As Range

Dim LastCopyRow As Long
Dim LastCopyColumn As Long
Dim x As Integer




Set SourceSht = ThisWorkbook.Worksheets("Sheet1")



LastCopyRow = SourceSht.Cells(Rows.Count, "A").End(xlUp).Row
LastCopyColumn = SourceSht.Cells(1, Columns.Count).End(xlToLeft).Column


Set rngCopy = SourceSht.Range("A3:" & SourceSht.Cells(LastCopyRow, LastCopyColumn).Address)


For Each x In rngCopy

If x.Value >= 10 And x.Value < 50 Then
x.Interior.Color = RGB(183, 222, 232)
ElseIf x.Value <> "" And x.Value < 10 Then
x.Interior.Color = RGB(255, 255, 0)
End If
Next x

End Sub

 

[hiker95]

Apple1,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Try changing this next line of code:

Dim x As Integer

To this:

Dim x As Range

We can not tell where your raw data is located, sheet name, cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.

If the above code change does not work correctly, then:

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) for the results that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

To test the above:
Test Here


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[sericom]

Not sure if it's your problem, but your using Row 1 to determine the last column. If this isn't right then edit the following line

LastCopyColumn = SourceSht.Cells(1, Columns.Count).End(xlToLeft).Column

 

[Apple1]

1. What version of Excel, and, Windows are you using?
Excel 2010, Windows 7

2. Are you using a PC or a Mac?
PC

3. It doesn't work even after I changed to

Dim x As Range

Sorry I really can't upload the screenshots. The dropbox link below also doesn't work.

But basically
Raw dataset, no special formatting:

I have data in
A3: C4

A7:C8

A title in A6.

Desired results:

Cells with value< 10: yellow
Cells with 10<=values< 50: blue

Currently I have data in Cell B3 and B7= 10, so it should be highlighted blue. However, that isn't happening. Only the cells that meet the criteria in Column A get highlighted yellow or blue.

Thank you

Apple1,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Try changing this next line of code:

Dim x As Integer

To this:

Dim x As Range

We can not tell where your raw data is located, sheet name, cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.

If the above code change does not work correctly, then:

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) for the results that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

To test the above:
Test Here


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Apple1]

Raw Dataset:
1 10 100
15 59 200

title 1
1 10 100
15 59 200

 

[sericom]

Change this

LastCopyColumn = SourceSht.Cells(1, Columns.Count).End(xlToLeft).Column

to

LastCopyColumn = SourceSht.Cells(3, Columns.Count).End(xlToLeft).Column

That will look in row 3 for the last used column

 

[hiker95]

Apple1,

I am not going to try, and, figure out where your raw data, and, results, should actually be.

If you can not supply screenshots, or, your workbook, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[SpreedsheetCrusader]

if this is my sheet content:

Excel 2010
	A	B	C	D	E
1
2		last col	4
3		last row	10
4
5		1	10	100
6		15	59	200
7
8		title 1
9		1	10	100
10		15	59	200
11
12
Sheet1

then using this code i can find the last column # and the last row # respectively :

Sub findrowcol()

Sheets("sheet1").UsedRange


Dim sht As Worksheet
Set sht = Sheets("sheet1")


Cells(2, 3).Value = sht.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
Cells(3, 3).Value = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


End Sub