VBA to Select cells

[end922]

Greetings, I am very new to vba in Excel. It's a whole new world.
I am trying to see how would I code a macro to select>copy all rows and columns in a spreadsheet that have data in them after I have just deleted unnecessary rows and columns? example when I am in cell A1 and I press shift,ctrl,End, it selects all the columns I want and the blank ones at the end and bottom.

Any help is greatly apprecaited.
Thanks
Eric

 

[SkipVought]

Hi,

Turn on your macro recorder and have at it. Tools > Macro > Record a new macro...

Post back with your recorded code to get help customizing if necessary.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[end922]

Hey Skip, I actually solved it by when I do my select I hold shift,ctl,down arrow....then do shift, ctl, arrow right. That allowed me to only select the columns and rows I needed.
I have a new question. If the person opening the report does nto want to save at the time they open it, but I still want them to have the option to do so? When they go to over write the file now the macro bombs out.

Thanks
Eric

Sub CleanUp()
    
    
    Selection.AutoFilter Field:=3, Criteria1:="<>*JAX*", Operator:=xlAnd
    Range("C6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    ActiveSheet.ShowAllData
    Range("C6").Select
'end of 1st step

    Rows("5:5").Select
    Selection.AutoFilter
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Rows("5:5").Select
    'Selection.Delete Shift:=xlUp
    Rows("1:3").Select
    Range("A3").Activate
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Project Name"
    With ActiveCell.Characters(Start:=1, Length:=12).Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    Columns("B:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Project Name"
        
    
    Range("A1").Select
 'Last Step
        Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Cells.Select
    Selection.ColumnWidth = 120.29
    Cells.EntireColumn.AutoFit
    Cells.Select
    Cells.EntireRow.AutoFit
    Range("C1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ChDir "C:\Documents and Settings\daileri\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\daileri\Desktop\SOM Tollgate Report.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
End With
  Range("A1").Select

End Sub

 

[SkipVought]

I do not understand your question.

Please identify the statement that errors and the exact error message.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[end922]

At the bottom, Active workbook save as filename...
A message box pops up and ask if they want to overwrite, yes, no, cancel. If they select yes then all is well. If they select No then the macro errors out that and thats it.

Thanks
Eric

 

[end922]

Nevermind. I wont do the save in the macro not that important. I just found out they will put their own twist on items then save.

I do appreciate your assistance.

Thanks
Eric

 

[Salut39]

Hi Eric,

You can select all cells that have data by: [SHIFT] + [END],[HOME].

Yuri