Excel 2013: Application.onkey - using shift and spacebar to select some rows 2

[pendle666]

Hello

In my macro, I sort by 'leaving date' all those leavers which I then want to remove.

Once sorted, I select all the dates using

[pre]Range(Selection, Selection.End(xlDown)).Select[/pre]

Now manually recording the macro, I use Shift and Spacebar to then select all the rows and then delete them.

But the recorded code specifies the rows - eg 2-235, but in another version of the report, the leavers might go up to row 300.

I've had a look in the help and there is the code [pre]application.onkey[/pre] but it doesn't seem to include a spacebar option.

Am I trying to do something impossible?

thank you for helping

____________
Pendle

 

[SkipVought]

Hi,

'
    With ActiveSheet
        Range( _
          .Cells(2, 1), _ 
          .Cells(.Cells(2, 1).CurrentRegion.Rows.Count, 1)) _
        .EntireRow.SpecialCells (xlCellTypeVisible).Delete
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

That's great, thank you very much

thank you for helping

____________
Pendle

 

[pendle666]

Hello

I'm needing to return back to this as the code:

[pre] With ActiveSheet
Range( _
.Cells(2, 1), _
.Cells(.Cells(2, 1).CurrentRegion.Rows.Count, 1)) _
.EntireRow.SpecialCells (xlCellTypeVisible).Delete
End With[/pre]

is actually deleting all the rows in the worksheet rather than the selected ones.

What is running before is:

Find the column with the termination dates and sort into A-Z (the dates are all together)

[pre] Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("2:331").Select
Range("L2").Activate
Selection.Delete Shift:=xlUp
Range("A2").Select
[/pre]

However, while in this case rows 2:331 have termination dates in this and these are the rows I don't want, it may be that in a further version of the report rows 2:250 are terminations.

The code SkipVought kindly provided is actually deleting all the worksheet. I tried to use the

[pre] Rows.EntireRow.Select[/pre]

but that also is selecting the entire worksheet rather than the terminated dates.

Can someone point me in the right direction?


thank you for helping

____________
Pendle

 

[SkipVought]

For some reason, I believe I was thinking that you were using a FILTER to select the date range in your table, ie to make Termination Dates visible.

Once sorted, I select all the dates using

Range(Selection, Selection.End(xlDown)).Select

Now manually recording the macro, I use Shift and Spacebar to then select all the rows and then delete them.

How is your macro to know what row to stop at?

What is the logical definition of Termination Date?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Hi

In my manually recorded macro I highlight the column names row and switch on filters.

Then search for termination and sort A-Z smallest to largest and that puts the ones with dates at the top.

When recording the deletion the code looks like this:

Rows("2:331").Select

and the CTRL Spacebar highlights the rows and removes it.

So if I alter the procedure to ignore blanks and then use your original code to delete the lines?


thank you for helping

____________
Pendle

 

[SkipVought]

Without seeing your data, I cannot advise.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Hi

Here is a bit of dummy data:


[pre]
A B C D E F
1 SURNAME NAME EE NO START DATE TERMINATION DATE
2 BROWN JOHN 12345 31/01/17 12/10/17
3 JONES FRED 39492 14/07/17 15/12/17
4 SMITH ANDREW 43243 01/02/16 12/06/17
5 ARDEN MICKY 43980 12/05/16 10/07/17
6 GREEN DAVID 47919 03/03/16

[/pre]
At this point in my macro the data has been sorted by termination date so all those with dates in column F are at the top.

If I use
[pre]
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select

[/pre]
It selects everything in column F with something in it - in this example F2 to F5.

What I'm trying to achieve is the equivalent of SHIFT SPACEBAR so that rows 2 to 5 are selected.

When using the macro recorder it gives me
[pre]
Rows("2:5").Select
[/pre]

Which is fine for the current data, however, there will be future reports where there might be termination dates in say rows 2 to 10. It's this I need to account for which is why I'm trying to find something equivalent to SHIFT SPACEBAR. I've tried sendkeys but there's nothing for spacebar.

Does this make sense?





thank you for helping

____________
Pendle

 

[SkipVought]

First, you do not need to SORT your data.

Second, you do need to use the AutoFilter. I also generally convert ALL my tables to Structured Tables via Tables > Table.

Third, make visible only those rows where termination data is not blank.

Fourth, use my original code...

'
    With ActiveSheet
    'filter termination dates
        .ListObjects(1).Range.AutoFilter Field:=5, Criteria1:="<>"
    'delete visible data rows in table
        Range( _
          .Cells(2, 1), _
          .Cells(.Cells(2, 1).CurrentRegion.Rows.Count, 1)) _
        .EntireRow.SpecialCells(xlCellTypeVisible).Delete
    'show remaining data
        .Cells(1, 1).Select
        .ShowAllData
    End With

But do you REALLY want to delete data? Data is usually pretty valuable. If you have a Filter, all you need do is filter out the non=blank data and then you have the ones not yet terminated.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

That's done the trick.

Thank you very much for your help with this.

thank you for helping

____________
Pendle

 

[SkipVought]

Here's code using Structured Table references to delete the data...

    Dim tName As String, tCol As Integer
    
    With ActiveSheet
        tName = .ListObjects(1).Name
        tCol = Range(tName & "[TERMINATION DATE]").Column
    'filter termination dates
        .ListObjects(1).Range.AutoFilter Field:=tCol, Criteria1:="<>"
    'delete visible data rows in table
        Range(tName & "[#Data]") _
        .EntireRow.SpecialCells(xlCellTypeVisible).Delete
    'show remaining data
        .Range(tName & "[#ALL]").Select
        .ShowAllData
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]