Quick Bytes IT Stuff

Raw data that comes in pattern is easy to handle for further processing. For example, if you have a data that has final value in every 7 th row, so basically 1 to 6 is not needed and 7 th row is needed, likewise 8 th row to 13 th row is not needed and 14 th row is needed and the pattern goes on that every 7 th row is the final data from the previous 6 rows. If the pattern continues, it will be like: 1-6, 7 final value, 7 -13, 14 final value, 15 to 20, 21 final value, 22 to 27, 28 final value and the pattern goes on up to hundreds or thousands. It will be time consuming to get every 7th row manually. For graphical representation it will be like: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 From the image above every 7 th row (green color) is needed and the rest is discarded. Other issue comes in if the data has to be processed further in another sheet,...

Have you received an email saying please find the picture below, but the picture is on the attachment not on the body of email. Or sending out a nice an beautiful layout of graphics hoping to impress a colleague or client only to find out that the the graphics doesn't display correctly in Outlook or either in the mobile phone. HTML Tables has been a big help for web designers or developers before the CSS era or other good platforms at this time. Well, if picture is not displaying properly in Outlook or mobile phone. Embedding or inserting the picture in a Table, seems to be a good quick solution in inserting pictures via Outlook and it will display properly in mobile phone also. When composing email in Outlook, click on the Insert option and click on insert table. If sending two pictures, you can insert a two rows or two columns and insert the picture on each row or column. After sending out the email, check the email in Outlook or mobile phone and if everything goes ok...

In VB.net or any programming language, if the program requires getting data from external programs, results or data from third party software that needs to be processed  may or may not have expected desired output. For example, getting a string from a third party software. String is quite straight forward and it's quite basic. But the issue arises,  if the desired output requires a specific string or text and other text should be discarded or deleted. Regex or any other methods might help on getting the specific text or data. One basic issue, if the string needs to be formatted in one line but a line feed or a new line keeps breaking the line. The first instinct that I think of, is to use "Trim" function but it doesn't solve the issue. Trim, Ltrim or Rtrim just delete spaces but it doesn't delete line feed. In VB.Net a line feed can easily be removed by this code snippet below: Dim strNoLineFeed As String = String_with_Line_Feed.Replace(vbCr, "...

Excel VBA code below will replace the first or last character of the cell value. Use a test workbook with dummy data, to test the VBA code. Works fine on Excel 2010. Sub replaceChar() 'Replace Last Character Dim i, ilength As Integer Dim strCellValue, yCutString, zValue As String Dim iRow, myColumn As Long 'specify the column where the values will be changed myColumn = 2 'get the last row iRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row For i = 1 To iRow On Error Resume Next strCellValue = Cells(i, myColumn).Value ilength = Len(strCellValue) - 1 'left - will replace last character on the string yCutString = Left(strCellValue, ilength)   ' LastChar is the string that will be added to the end of the cell value 'Replace this with any desired value zValue = yCutString + "LastChar" 'Display the value of the processed string 'press ctrl+break to stop the loop MsgBox zValue 'uncomment this line to replace the value with ...

VBA code below will get and set paper tray settings of a word document. Macro tested on Word 2010. Values 7 and 1 are the value output from the message box. I'm not sure whether values are dependent on printer driver. On my word 2010 those are the values that can be used to set paper tray settings. 7 - will set to "Automatically Select" 1 - will set to Tray1 If got documents received from other party that always retain the printer settings. A macro will be useful to change the settings or by doing it manually by going to Page Setup and change the paper tray settings. Sub getpapertray() Dim xtray1, xtray2 xtray1 = ActiveDocument.PageSetup.FirstPageTray xtray2 = ActiveDocument.PageSetup.OtherPagesTray MsgBox xtray1 MsgBox xtray2 'Set the tray to Automatically Select ActiveDocument.PageSetup.FirstPageTray = 7 'Set the tray to Tray 1 ActiveDocument.PageSetup.OtherPa...

Sub Macro_VBA_Delete_Page() Selection.GoTo wdGoToPage, wdGoToAbsolute, 2 Selection.Bookmarks("\Page").Select Selection.Delete Unit:=wdCharacter, Count:=1 End Sub Selection.GoTo wdGoToPage, wdGoToAbsolute, 2 Line above will goto page number "2" Selection.Bookmarks("\Page").Select This will select the whole page. Selection.Delete Unit:=wdCharacter, Count:=1 As the keyword specified "Delete", it will delete the whole page   To delete 2 subsequent pages using VBA use code below: Sub Del_pages() Dim i  For i = 1 To 2 Selection.GoTo wdGoToPage, wdGoToAbsolute, 2 Selection.Bookmarks("\Page").Select   Selection.Delete Unit:=wdCharacter, Count:=1  Next End Sub Code above will delete page 2 and page 3 on the word document Selection.GoTo wdGoToPage, wdGoToAbsolute, 2 Code above will goto page 2 and once page 2 is deleted, page 3...

Need to clear or remove all comments in a workbook? If time is not a constraint, comments can be deleted manually. To do it manually, is to right click on the cell that has a comment and click "delete comment." It’s not so hard, right? What if there are 10 worksheets, in the workbook and every worksheet has 20 or more comments? Deleting the comments manually, would not be fun. VBA is an ideal way to clear or remove comments the easy and quick way. Here's a simple VBA code to do it. Don't run the code on production, and if the workbook and the comments are quite important. But if you exactly know what you are doing, go ahead. Or save a copy of the workbook and do the test. Once the comments are deleted it cannot be undone. Copy and paste the code to the VBA editor. ================================= Sub Delete_all_comments() 'VBA code below will delete all the comments 'For loop statement...