VBA - Need help selecting columns and rows

Lidsavr

Active Member
Joined
Jan 10, 2008
Messages
330
I am running Excel 2007.

I want to modify the code below to select one "less" row and cannot figure out how to do it. Can anyone help me?

I have a small table that depending on the week ranges between 2-18 rows deep. NOTE: I have another table starting on row 21 so the "End(xlUp) statement starts on Row 20).

Please click on the link below to see a picture of the current table (.jpg format):
https://docs.google.com/leaf?id=0B8ZFmctoHTEBNDNhNmE5NWQtNzUxZS00Mzc0LTk5YTctOTkzNzI4YTEyNzZh&sort=name&layout=list&num=50. (Sorry, I do not have any server space available, so I am using Google Docs to store the picture).

Here is the situation: I am only want to select the rows and columns A4:A9 (not selecting the Grand Total row or column)

My current code only will select "all the rows and the first four columns, but I want to eliminate the last row from the selection. (I will using the "BeginCell, EndCell ranges to create a chart later in the code). Here is my current code:

Code:
    If Cells(4, 1).Value = "DefectDescription" Then
        Set BeginCell = Cells(4, 1)
        Set EndCell = Cells(20, 4).End(xlUp)   [COLOR="Red"]'<---- I think this is where I need to subtract the row, but I do not know how! Am I wrong?[/COLOR]
        Range(BeginCell, EndCell).Select
    End If
    Sheets("232 Charts").Select
    Range("A3").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("232 Data").Range(BeginCell, EndCell), PlotBy _
        :=xlRows

Thank you,

Charles
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
jonmo1,
That's it! It works perfectly.

I have been trying different ways of coding to remove the row for almost 2 hours. I probably would have spent many more if it were not for you help.

Thank you!

Charles
 
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,152
Members
452,891
Latest member
JUSTOUTOFMYREACH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top