modifying a macro when there is only one column of data

[postermmxvicom]

I have modified a macro I recorded to make it better. However, one bug I can't seem to get rid of is when there is only one column of data it tries to copy all the empty cells in the workbook to the right. I have made several attempts to overcome this, but have only proven I don't know what I am doing.

Sub Q3GradePrep()
'
' Q3GradePrep Macro
' This macro copies q3 grades and preps them for uploading.
'
' Keyboard Shortcut: Ctrl+Shift+G

    'these store the dimensions of the original copied area
    'so that the formula is copied to the appropriate number
    'of cells on the final page
    Dim iNumberOfColumns As Integer
    Dim iNumberOfRows As Integer
    
    'this section should copy all the columns and rows from the cursor
    'position that contain data, however when there is only one row this code breaks
    'and attempts to copy the entire rest of the work book to the
    'right, below are two attempts at fixing that problem
    
    'attempt at automation to replace old code (below), also doesnt work
    'iNumberOfColumns = Selection.Columns.Count
    'If (iNumberOfColumns > 30) Then
    '    Do Something here to take care of single column cases
    'End If
    
    'attempted code to replace above old code (below), doesnt work
    'If (MsgBox("One Column?", vbQuestion + vbYesNo, "???") = vbYes) Then
    '    Range(Selection, Selection.End(xlToLeft)).Select
    'End If
    
    'old code
    Range(Selection, Selection.End(xlToRight)).Select
    
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    'lots of other code here I dont think is relevant
    'to the problem

End Sub

You can see in the comments of the code my attempts at fixing this. I thought I could just detect if it copied a ridiculous amount of columns I could have it undo that selection, but was unable to make it work.

Then I thought i could just have a yes/no box pop up and ask my if there was more that one column and go from there. I could not get that work either.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[SkipVought]

Hi,

You must test to determine if there is only one column or not...

if Selection.CurrentRegion.Columns.Count > 1 then
    Range(Selection, Selection.End(xlToRight)).Select
end if
    
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

I would not be using Select and Selection.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[postermmxvicom]

Thanks for your reply Skip,

I have a basic knowledge of VBA and no experience using it in excel. I will try your solution and see if I can get it to work. I will report back with my success (hopefully).

Most of the code I am using was generated by excel when I made the macro. I was able to make 99% of the modifications I needed, just this last one if getting me. Could you elaborate on your last statement, I do not know enough to understand it.

Thanks again for your time.



One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[SkipVought]

faq707-4105

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Just a suggestion - I think you were on the right track with trying to figure out up front how many rows and columns you have. Once you successfully do that, you can just define the range to copy - You don't have to worry about handling it differently depending on the number of columns.


Try this:

Sub Q3GradePrep()
    Dim iNumberOfColumns    As Integer
    Dim iNumberOfRows       As Integer

    '   Syntax for CELLS is Cells(RowIndex, ColumnIndex)
    iNumberOfColumns = Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    iNumberOfRows = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    Range(Cells(1, 1), Cells(iNumberOfRows, iNumberOfColumns)).Copy

End Sub

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.