74942

VBA code to overwrite cells through moving /shifting up a range of cells

Question:

Hi there i have a data table, as seen from the picture, which changes from time to time. For example if there new data coming up for March'15 i would have to copy and overlap cells from April'14 onwards to March'14. Thereafter i would fill in the information for march'15 on the blank cell that was previously filled with feb'15 information. I was wondering if there is any vba code to help move or shift the range to the preferred row of cells that already has existing data ( more of a code to overlap/ overwrrite cells through moving/shifting up) .<br /><a href="https://i.stack.imgur.com/O0A9O.png" rel="nofollow"><img alt="Data Table " class="b-lazy" data-src="https://i.stack.imgur.com/O0A9O.png" data-original="https://i.stack.imgur.com/O0A9O.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

Was thinking of Activecell.offset but i am not sure if it can shift up as a range of cells.

Answer1:

In that case, use a simple loop to copy the values. The last row (13) is overwritten with the empty values of row 14.

Public Sub MoveUp() Dim sh As Worksheet Dim x As Long, y As Long Set sh = ActiveSheet ' or the specific sheet ' The 12 months For y = 2 To 13 ' Your 7 columns For x = 1 To 7 sh.Cells(y, x) = sh.Cells(y + 1, x) Next x Next y With sh.Cells(13, 1) .Select ' This only works if your column A contains dates (not strings) .Value = DateAdd("m", 1, sh.Cells(12, 1)) End With End Sub

Remove the DateAdd line at the end if your column A contains strings, not dates.

Answer2:

There are probably multiple ways to do this, but a simple one is (straight from the macro recorder of moving a range and overwriting the target):

Range("A3:G13").Cut Destination:=Range("A2:G12") Range("A13").Select

Recommend

  • Formula to remove entire words that start with certain characters
  • Importing a module into access programmatically from a *.cls or similar file
  • Access 2007 forms with parameterized RecordSource
  • Excel VBA : How to copy href from HTML by referencing the class name
  • Editable Combo Box Javascript and HTML
  • Is it possible to send skype chat messages from a linux server without X?
  • What version of Java should I use with Cassandra 2.0?
  • Is there a way to call library thread-local init/cleanup on thread creation/destruction?
  • What is this strange character in chrome's resource css viewer?
  • How to repeat sections of a SQL query across UNIONs? (DRY in SQL)
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • Count New Lines in Text File
  • apply a javascript function to draggable copy
  • Plotting densities in R
  • Angular2 - Template reference inside NgSwitch
  • Set focus to first invalid form element in AngularJS
  • Android changing fragment order inside FragmentPagerAdapter
  • Excel's Macro-Recorder usage
  • Create DicomImage from scratch using Dcmtk
  • Groovy: Unexpected token “:”
  • How to rebase a series of branches?
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • VBA Convert delimiter text file to Excel
  • Unanticipated behavior
  • Transpose CSV data with awk (pivot transformation)
  • Use group_by to filter specific cases while keeping NAs
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Python: how to group similar lists together in a list of lists?
  • log4net write single file for each call to log.info
  • Benchmarking RAM performance - UWP and C#
  • Acquiring multiple attributes from .xml file in c#
  • Understanding cpu registers
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • Can't mass-assign protected attributes when import data from csv file
  • Reading document lines to the user (python)
  • Unable to use reactive element in my shiny app
  • java string with new operator and a literal