Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > I want VB to select data automatically to the end of the row
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default I want VB to select data automatically to the end of the row
Hi,
I have recorded a macro, but I want to improve it. I want VBA to select my
data to the last row automatically instead of manually changing the last cell
number. This is the VBA code:
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(1).Values = "=Dental!R16C2:R74C2"
ActiveChart.SeriesCollection(2).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(2).Values = "=Dental!R16C3:R74C3"
ActiveChart.SeriesCollection(3).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(3).Values = "=Dental!R16C8:R74C8"
ActiveChart.SeriesCollection(4).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(4).Values = "=Dental!R16C9:R74C9"
Windows("Dental RADAR Monthly Report 200810.xls").SmallScroll Down:=36
ActiveWindow.Visible = False

Thus, instead of changing the number 74 (last row) I want VBA to
automatically do it. Is that possible?

Thanks
Miguel
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,276
Default I want VB to select data automatically to the end of the row
Hi Miguel,
Try
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name And sh.Visible = True Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Range("A" & StartRow),
sh.Range("IV" & shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the A column
'DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value =
sh.Name
If the answer was helpful please press yes

"Miguel" wrote:

Hi,
I have recorded a macro, but I want to improve it. I want VBA to select my
data to the last row automatically instead of manually changing the last cell
number. This is the VBA code:
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(1).Values = "=Dental!R16C2:R74C2"
ActiveChart.SeriesCollection(2).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(2).Values = "=Dental!R16C3:R74C3"
ActiveChart.SeriesCollection(3).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(3).Values = "=Dental!R16C8:R74C8"
ActiveChart.SeriesCollection(4).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(4).Values = "=Dental!R16C9:R74C9"
Windows("Dental RADAR Monthly Report 200810.xls").SmallScroll Down:=36
ActiveWindow.Visible = False

Thus, instead of changing the number 74 (last row) I want VBA to
automatically do it. Is that possible?

Thanks
Miguel
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default I want VB to select data automatically to the end of the row
Eduardo,
the program didn't work. The LastRow subfuntion is not defined. Is there
another way to make it work?

Thanks

"Eduardo" wrote:

Hi Miguel,
Try
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name And sh.Visible = True Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Range("A" & StartRow),
sh.Range("IV" & shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the A column
'DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value =
sh.Name
If the answer was helpful please press yes

"Miguel" wrote:

Hi,
I have recorded a macro, but I want to improve it. I want VBA to select my
data to the last row automatically instead of manually changing the last cell
number. This is the VBA code:
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(1).Values = "=Dental!R16C2:R74C2"
ActiveChart.SeriesCollection(2).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(2).Values = "=Dental!R16C3:R74C3"
ActiveChart.SeriesCollection(3).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(3).Values = "=Dental!R16C8:R74C8"
ActiveChart.SeriesCollection(4).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(4).Values = "=Dental!R16C9:R74C9"
Windows("Dental RADAR Monthly Report 200810.xls").SmallScroll Down:=36
ActiveWindow.Visible = False

Thus, instead of changing the number 74 (last row) I want VBA to
automatically do it. Is that possible?

Thanks
Miguel
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,510
Default I want VB to select data automatically to the end of the row
Hi Miguel,

I think you are looking for someting like this. Note that the space and
underscore at the end of a line is a line break in an ohterwise single line
of code. I use them in posts so that the data fits and can be copied into
your VBA editor without the hassel of joining up lines that break due to
posting.

If you are uncertain of what I have done to the range then look up
concatenating strings because that is all I have done.


Dim lngLastRow As Long 'Variable for last row of data

'Assign last row number with data to variable
'It is basically like selecting the last cell
'on the worksheet in column 1 then hold Ctrl key
'and press up arrow and then identify the row number.

With Sheets("Dental")
lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(1).Values = _
"=Dental!R16C2:R" & lngLastRow & "C2"
ActiveChart.SeriesCollection(2).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(2).Values = _
"=Dental!R16C3:R" & lngLastRow & "C3"
ActiveChart.SeriesCollection(3).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(3).Values = _
"=Dental!R16C8:R" & lngLastRow & "C8"
ActiveChart.SeriesCollection(4).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(4).Values = _
"=Dental!R16C9:R" & lngLastRow & "C9"
ActiveWindow.Visible = False

--
Regards,

OssieMac


"Miguel" wrote:

Eduardo,
the program didn't work. The LastRow subfuntion is not defined. Is there
another way to make it work?

Thanks

"Eduardo" wrote:

Hi Miguel,
Try
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name And sh.Visible = True Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Range("A" & StartRow),
sh.Range("IV" & shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the A column
'DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value =
sh.Name
If the answer was helpful please press yes

"Miguel" wrote:

Hi,
I have recorded a macro, but I want to improve it. I want VBA to select my
data to the last row automatically instead of manually changing the last cell
number. This is the VBA code:
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(1).Values = "=Dental!R16C2:R74C2"
ActiveChart.SeriesCollection(2).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(2).Values = "=Dental!R16C3:R74C3"
ActiveChart.SeriesCollection(3).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(3).Values = "=Dental!R16C8:R74C8"
ActiveChart.SeriesCollection(4).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(4).Values = "=Dental!R16C9:R74C9"
Windows("Dental RADAR Monthly Report 200810.xls").SmallScroll Down:=36
ActiveWindow.Visible = False

Thus, instead of changing the number 74 (last row) I want VBA to
automatically do it. Is that possible?

Thanks
Miguel
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,276
Default I want VB to select data automatically to the end of the row
Hi Miguel,
I don't know what happen but when copy the code something was missing add
this to the beginning
' Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long


"OssieMac" wrote:

Hi Miguel,

I think you are looking for someting like this. Note that the space and
underscore at the end of a line is a line break in an ohterwise single line
of code. I use them in posts so that the data fits and can be copied into
your VBA editor without the hassel of joining up lines that break due to
posting.

If you are uncertain of what I have done to the range then look up
concatenating strings because that is all I have done.


Dim lngLastRow As Long 'Variable for last row of data

'Assign last row number with data to variable
'It is basically like selecting the last cell
'on the worksheet in column 1 then hold Ctrl key
'and press up arrow and then identify the row number.

With Sheets("Dental")
lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(1).Values = _
"=Dental!R16C2:R" & lngLastRow & "C2"
ActiveChart.SeriesCollection(2).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(2).Values = _
"=Dental!R16C3:R" & lngLastRow & "C3"
ActiveChart.SeriesCollection(3).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(3).Values = _
"=Dental!R16C8:R" & lngLastRow & "C8"
ActiveChart.SeriesCollection(4).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(4).Values = _
"=Dental!R16C9:R" & lngLastRow & "C9"
ActiveWindow.Visible = False

--
Regards,

OssieMac


"Miguel" wrote:

Eduardo,
the program didn't work. The LastRow subfuntion is not defined. Is there
another way to make it work?

Thanks

"Eduardo" wrote:

Hi Miguel,
Try
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name And sh.Visible = True Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Range("A" & StartRow),
sh.Range("IV" & shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the A column
'DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value =
sh.Name
If the answer was helpful please press yes

"Miguel" wrote:

Hi,
I have recorded a macro, but I want to improve it. I want VBA to select my
data to the last row automatically instead of manually changing the last cell
number. This is the VBA code:
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(1).Values = "=Dental!R16C2:R74C2"
ActiveChart.SeriesCollection(2).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(2).Values = "=Dental!R16C3:R74C3"
ActiveChart.SeriesCollection(3).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(3).Values = "=Dental!R16C8:R74C8"
ActiveChart.SeriesCollection(4).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(4).Values = "=Dental!R16C9:R74C9"
Windows("Dental RADAR Monthly Report 200810.xls").SmallScroll Down:=36
ActiveWindow.Visible = False

Thus, instead of changing the number 74 (last row) I want VBA to
automatically do it. Is that possible?

Thanks
Miguel
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to select data in non adjacent rows of a column automatically jdpf Excel Discussion (Misc queries) 5 March 23rd 09 10:32 PM
Automatically select read-only [email protected] Excel Discussion (Misc queries) 2 February 22nd 06 10:21 AM
Need Way to Automatically Select Data Art Stewart Excel Worksheet Functions 4 March 21st 05 01:32 AM
Need Way to Automatically Select Data Arthur Moore Excel Discussion (Misc queries) 0 March 19th 05 12:32 AM
automatically select last value in a column Jon Horvath Excel Worksheet Functions 2 December 23rd 04 07:47 PM


All times are GMT +1. The time now is 04:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement