delete first text record in a range

MATEAPOT

New Member
Joined
Jul 5, 2008
Messages
14
HI GUYS
Iam very new to coding.And i need help with code which,when i enter a new record,will delete the first record in row 1 then move record 2 to row 1,record 3 to row 2 and so on.The new record should always occupy row 7..Sheet1 is linked to onother sheet where the records,NAME,BANK and the rest are input from.Now sheet1 will be printed after a new record is input and should show that record on level7(row 9).If there is a record already on level 7 it should be moved one row up to level 6(row 8).This displacement should affect all records.The first record on level 1(row3) should be deleted OR filtered to make way for the record on level2(row4).can a macro do this?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
If I understand you correctly, you always want the most recent 7 records to be used in a printout/report. Is it necessary to hold the most recent 7 on sheet1 or do you simply want to print them out?

If you enter all records in the same order, eg, every new record is added below the last record or always inserted a new row1 and put the new data on row1, you may use the macro to select the newest 7 records from your input sheet and either set the print range and print them out, or copy them to sheet1 row1 overwriting what was there (always 7 rows) and print sheet1.

You would want to add a custom toolbar button or add a command button to your input sheet to run your macro that produces the report and if still desired, copies the newest 7 to sheet1. What is it you actually need to do?
 
Upvote 0
Do the following in a new FRESH Workbook, NOT your production ss!!

In a sample wb I entered into Sheet1 Range(B2:D9)


Level Amount Lookup << Row 2
1 555 Triple5 << In B3 =Row()-2 D3 =Vlookup(C3,Sheet2!$A$1:$B$7,2,False) copying down to row 9 on Col B and D
2 666 Triple6
3 777 Triple7
4 111 Triple1
5 222 Triple2
6 333 Triple3
7 444 Triple4

Paste the below code into the Sheet1 Code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$10" Then
Application.EnableEvents = False
With Target
.Offset(0, -1).Formula = "=row()-2"
.Offset(0, 1).Formula = "=Vlookup(C" & Target.Row & ",Sheet2!$A$1:$B$7,2,False)"
End With
Range("3:3").EntireRow.Delete
End If
Application.EnableEvents = True
End Sub

Enter a new value into Cell C10 and the macro will perform for you...
 
Upvote 0
Every new record must be always be inserted below the last record and i was holding the 7 records on sheet1 for lookup purposes.All 7 records won't be entered at once and printed,the printing will be done after entry of each record.And the printout should always have 7 records with the last record appearing at the bottom.
The macro should select the newest record from my input sheet copy it to sheet1 last row (say row7) and print sheet1.if there is a record on the last row(row7)on sheet1 it should be moved up to row 6.This displacement should affect all records and record one should be deleted to make way for the record in the second row.
i need a command button on my input sheet that will run the macro that does this,including the printing.
hope this clarifies the probleM.
 
Upvote 0
M,

I assume you haven't spent much time writing macros, so this code is heavily commented to explain what's happening. The added comments makes it look a lot more complicated than it is. Comments have an apostophe (single quote) in front of them and are not part of the program. They should also appear green.

You need to copy this code and paste it into the code module for Sheet2. You get to the code module by picking the Excel Menu item TOOLS, then MACRO, then VISUAL BASIC EDITOR. Look for the workbook name you are using and doubleclick Sheet2. A window will popup that will be used to hold any code associated with Sheet2.

Copy the code below and paste it into the Sheet2 code window. Switch back to Excel from the Visual Basic Editor and run the macro called Sheet2 Update to test the code.

Have fun.

RLP




Code:
[color=green]'Assumption: your new records are on Sheet2.[/color]
[color=green]'    Change the word "Sheet2" to whatever name you[/color]
[color=green]'    give the tab for your input sheet.[/color]
[color=green]'Assumption: your column titles (headers,fieldnames,whatever) are on row1[/color]
[color=green]'Assumption: your first record on the input sheet begins in row2[/color]

[color=green]'Copy this code and put it into the module for your input sheet[/color]
[color=green]'(Sheet2 or whatever you call it)[/color]

[color=darkblue]Sub[/color] Update()
  [color=darkblue]Dim[/color] FirstRow, LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
  
  [color=green]'Select your input sheet[/color]
  
  Sheets("Sheet2").Select
  [color=green]'Find the last record that was input[/color]
  [color=darkblue]With[/color] ActiveSheet
    .Range("A65536").Select
    Selection.End(xlUp).Select
    LastRow = Selection.Row

  [color=green]'pick the last 7 records that were input[/color]
  [color=green]'if 7 haven't been input yet, take what's there[/color]
  [color=green]'including blank rows until you have 7 rows[/color]
   
    [color=darkblue]If[/color] LastRow < 8 [color=darkblue]Then[/color]
      FirstRow = 2
      LastRow = 8  [color=green]'lastrow is record#7, need previous 6[/color]
    [color=darkblue]Else[/color]
      FirstRow = LastRow - 6  [color=green]'lastrow is record#7, need previous 6[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
  
  [color=green]'Assuming you are using up to 8 (A-H) columns on your input sheet[/color]
  [color=green]'If not, you can leave as is because it won't hurt anything unless you need[/color]
  [color=green]'more than 8 columns, then change H to whatever (L or N, etc)[/color]
  [color=green]'Also, if you don't use column A and begin on column B, change the A to B[/color]
  [color=green]'Change the H to the last column you are using[/color]
    
  [color=green]'Now, pick the column titles[/color]
    .Range("A1:H1").Select
    Selection.Copy
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  
  [color=green]'move to sheet1[/color]
  Sheets("Sheet1").Select

 [color=green]'If you changed A to B, change it here too...[/color]

  ActiveSheet.Range("A1").Select
  ActiveSheet.Paste
  
  [color=green]'back to Sheet2[/color]
  Sheets("Sheet2").Select

 [color=green]'If you changed A to B, change it here too...[/color]
 [color=green]'If you changed H to something, change it here too...[/color]

 [color=green]'Now, copy last 7 records[/color]
  ActiveSheet.Range("A" & [color=darkblue]CStr[/color](FirstRow) & ":H" & [color=darkblue]CStr[/color](LastRow)).Select
  Selection.Copy
  
  [color=green]'move to sheet1[/color]
  Sheets("Sheet1").Select

 [color=green]'If you changed A to B, change it here too...[/color]
 [color=green]'If you changed H to something, change it here too...[/color]

 [color=green]'Now, paste the last 7 rows of data from sheet2[/color]
 [color=darkblue]With[/color] ActiveSheet
    .Range("A2").Select
    .Paste
  [color=green]'Now, set the printarea on sheet1 for[/color]
  [color=green]'Titles + 7 records[/color]
    .Range("A1:H8").Select  
    .PageSetup.PrintArea = "$A$1:$H$8"

  [color=green]'this is just to unselect A1-H8 so you don't delete by accident[/color]
    .Range("A9").Select
  [color=green]'this puts you into print preview mode[/color]
    .PrintPreview
  [color=green]'you can change preview to .PrintOut Copies:=1 to simply print[/color]
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  
  [color=green]'back to sheet2[/color]
  Sheets("Sheet2").Select

  [color=green]'this is just to unselect so you don't delete by accident[/color]
  ActiveSheet.Range("A" & Cstr(LastRow+1)).Select

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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